Risc World 3

home *** CD-ROM | disk | FTP | other *** search

/ Risc World 3 / Risc World 3.iso / SOFTWARE / ISSUE6 / POWERBASE / OPRODOC / Manual (.txt) next >

Wrap

Ovation Pro Document | 2003-03-13 | 865KB | 9,423 lines

OvationPro (23-March-02) 1.1 to 20.102 Generic Black White Transparent Green Magenta Yellow Registration RGB000000 RGBFFFFFF rgb0/0/0 rgb100/100/100 Trinity.Medium.Italic Trinity.Bold.Italic Homerton.Bold Homerton.Bold.Oblique Homerton.Medium Trinity.Medium Trinity.Bold Corpus.Medium Corpus.Bold BodyText Main Heading Sub-Heading 1in indent Hanging indent Contents2 Effect15300 Effect17 Sub-SubHeading Effect22 Page number Corpus.Medium HalfSpace Effect663 Centre Homerton.Medium NoGap SubSubSub Justify Contents0 Contents1 Trinity.Medium Effect946 Effect13590 Index Footnote Italic Index layout Effect12945 Effect12948 Underline Effect15296 Effect13078 Effect417 Effect429 Effect435 Half space Effect16 Effect21 Hanging indent2 Effect15309 Effect15312 Index headings on.Medium Table Table2 W:? \ Ot 0' /I!x' I9#,( >b'@) :7,$+ 1$-p+ .n6 . s4/<$0 OD<80 +g<d0 !MainDict !Ignore {filename} P {filename} P{pagenumber} {datetime} DrawPlus Database for RISC OS computers with RISC OS 3.1 or later Derek Haslam Revised January 2003 to comply with v. 9.14 of Powerbase Contents Ch 1 Introduction to Powerbase Description and installation 1.1.1 Installation 1.1.2 Tutorials 1.1.3 Disclaimer Starting and ending a work session 1.2.1 Loading Powerbase 1.2.2 Opening a database 1.2.3 Closing a database 1.2.4 Quitting Powerbase Obtaining help 1.3.1 Using the Acorn Help application 1.3.2 Using the Helpreader text 1.3.3 Emailing Powerbase Support and accessing our website Ch 2 Browsing and Editing Subfiles, record numbers and keys 2.1.1 Subfiles 2.1.2 Record numbers 2.1.3 Moving about the database 2.2.1 Browsing 2.2.2 Cross-referencing records 2.2.3 Changing keys 2.2.4 Changing subfiles 2.2.5 Using more than one subfile 2.2.6 Naming subfiles Searching for a record 2.3.1 Searching by key 2.3.2 Searching by record number 2.3.3 Searching by filter Editing the database fundamental operations 2.4.1 Adding new records 2.4.2 Deleting records 2.4.3 Altering existing records 2.4.4 Undoing mistakes Editing the database special features 2.5.1 Using a template 2.5.2 Copying fields 2.5.3 Copying an entire record 2.5.4 Choosing the field where editing starts 2.5.5 Changing many records at once 2.5.6 Moving or deleting many records at once 2.5.7 Hiding sensitive data 2.5.8 Protecting data against deletion External fields 2.6.1 Linking files to the fields 2.6.2 Editing External fields 2.6.3 Clearing and exporting field contents 2.6.4 Editing scrollable lists Backing up Ch 3 Printing from the Database Output destination 3.1.1 The Window destination 3.1.2 Reloading saved reports 3.1.3 Calling up records from the report window 3.1.4 The Text file destination 3.1.5 The Printer destination Print formats 3.2.1 Horizontal 3.2.2 Vertical 3.2.3 Table 3.2.4 Label What types of field can be printed? Specifying which fields to print 3.4.1 Saving print selection files 3.4.2 Default selection Specifying which records to print 3.5.1 The construction of search formulae 3.5.2 Numeric and other special fields in search formulae 3.5.3 Using wild-cards in search formulae 3.5.4 Querying scrollable lists 3.5.5 Comparing the contents of two fields 3.5.6 Saving search formulae for re-use Query by example 3.6.1 What is QBE? 3.6.2 QBE vs QSF Other features of the Query panel and Match window 3.7.1 Printing records from more than one subfile 3.7.2 Including record number, key and subfile number Marking records for inclusion or exclusion 3.8.1 Arbitrary selection of records 3.8.2 Groups of records Printing only the displayed record The print options window 3.10.1 Scrollable lists 3.10.2 The rest of the print options 3.10.3 Saving print options files The printer setup window 3.11.1 Line length and point size of fonts 3.11.2 Print margins 3.11.3 Printing speed Field analysis reports Subsidiary indexes and printing speed Ch 4 Creating a New Database Creating the database application shell Designing the record layout 4.2.1 Simple field creation 4.2.2 Deleting, inserting and re-ordering fields. 4.2.3 Moving and re-sizing the bounding box 4.2.4 More about tags and descriptors 4.2.5 Other types of Editable field 4.2.6 Scrollable lists 4.2.7 Check-box fields 4.2.8 External fields 4.2.9 Remote fields 4.2.10 Auto-displaying External files 4.2.11 Computed fields 4.2.12 Stamp fields 4.2.13 Button fields 4.2.14 Mandatory fields A short-cut to a working database Specifying the database size Specifying the primary key 4.5.1 General procedure 4.5.2 Some illustrative examples 4.5.3 Using more than one field in a key 4.5.4 Further refinements 4.5.5 Other matters concerning keys Building the empty database Renaming a database Ch 5 Input Validation and Validation Tables Character validation Validation tables Creating a validation table Linking tables to fields 5.4.1 The Replace on entry feature 5.4.2 When to turn off the Exact match button 5.4.3 Conditional validation Displaying validation tables The validation table menu Loading validation tables Including validation table data in printouts Entering validation table data into a record Ch 6 Performing Calculations Calculated fields 6.1.1 Simple calculations using Numeric fields 6.1.2 Making calculations retrospective 6.1.3 Calculations using non-numeric fields (!) 6.1.4 Calculations involving times 6.1.5 Calculations involving dates Composite fields User functions Calculations for reports only Calculations from an Evaluate button Calculations on a column of a report Ch 7 Using extra indexes Indexing a field Automatic saving of indexes Ch 8 Exporting and Importing data - CSV files What are CSV files? Setting the CSV options Exporting data as a CSV file 8.3.1 Scrollable lists and CSV files Using CSV files to import data 8.4.1 Ensuring that the correct options are selected 8.4.2 Directing imported data to the correct fields 8.4.3 Importing data from plain text files 8.4.4 What if the imported data won t fit? Using CSV files to modify existing records Importing data into validation tables Creating a new, working database from a CSV file Other methods of exporting data Ch 9 Mail-Merging with Impression, Ovation and Ovation Pro Mail-merging with Ovation* 9.1.1 Preparing an Ovation document for mail-merging 9.1.2 Merging the data from Powerbase Mail-merging with Impression and Ovation Pro 9.2.1 Preparing the document for merging 9.2.2 Merging the data Mail-merging with other programs Ch 10 Utilities Changing the Primary Key Altering the record format Rebuilding the database Merging two databases Changing the Database Length Inspecting and balancing index trees Useful database details Finding duplicate primary keys 10.9 Libraries Ch 11 Passwords and related matters Levels of protection 11.1.1 Individual I.D.s and passwords Options selected from the password-setting window 11.2.1 Duplicate primary keys Logging database changes Ch 12 Script Files The !SCRIPT command 12.1.1 Filenames in script commands Commands used for reporting Setting print options Setting printer setup options Commands used mainly for editing Repeating operations using a loop Other commands Requesting user input from script files Ch 13 Subset databases Creating a subset Using a subset to shorten a database Ch 14 User choices Preferences 14.1.1 Control 14.1.2 Separators in date and time fields 14.1.3 Wild-cards 14.1.4 Option buttons 14.1.5 Save indexes (Default: Manual) 14.1.6 Start editing at 14.1.7 Application for ImpulseII data-merging 14.1.8 Save choices CSV options Print and Printer setup options Layout grid options Colours of key fields and table-linked fields Defining the function keys Overall control of the database Config files The Messages file 14.10 Giving a database a title screen 14.11 Saving and re-loading choices 14.11.1 Where are choices les saved? 14.11.2 Priority of choices 14.11.3 What does the Load default button do? 14.11.4 Preserving your own choices when upgrading Ch 15 Customising Powerbase A demonstration How is it done? What happens when Powerbase is run? What happens when a database is opened? Creating buttons What goes inside the IF...THEN structure? Detecting events Appendix A Powerbase as an Impulse server Description: Appendix B Keystroke equivalents Index ED- < Etr <LA Ch 1 Introduction to Powerbase Description and installation Powerbase is a database management system for use with RISC OS computers. The information in this manual applies to Powerbase v. 9.14, of which there are two variants: 9.14DA and 9.14SH. They differ only in their method of memory-mangement; 9.14DA uses dynamic areas and 9.14SH uses the sliding-heap module which has featured in Powerbase ever since it migrated to RISC OS from the BBC micro. If you use RISC OS 3.1 you need v.9.14SH. Please note that v.9.14DA is not compatible with RISC OS 3.1 . For RISC OS 3.5 or above, however, v.9.14DA is recommended. You may use v.912SH if you prefer (objections have been voiced concerning programs which make extensive use of dynamic areas) and both versions have been tested on RISC OS v.3.5 to 4.33 and can exploit the Users feature of the latter when saving choices les. The sliding-heap module used in 9.14SH is now 32-bit compatible so there should be no problem using it on an Iyonix. 1.1.1 Installation You may receive Powerbase and its accompanying documentation and sample databases in compressed form as a set of zip files. These need to be unzipped (de-archived) before you can use them. The following procedure is suggested: On your hard disc create a new directory called Powerbase and open it. Run the copy of !SparkPlug supplied on the distribution disc. Double click on one of the files you wish to unzip. A window will open showing the contents of the file. Select all the objects in the window and drag them to your newly-created Powerbase directory. Repeat steps (c) and (d) until you have de-archived all the material you require. 1.1.2 Tutorials Two tutorials are provided in a separate booklet. If databases are completely new to you we recommend you to work thought the Quick Tutorial which is based on the simple address-book database !Friends. If you are somewhat more experienced but are using Powerbase for the first time you will probably find Tutorial , using the !Elements database, more appropriate. 1.1.3 Disclaimer No warranty, express or implied, is made about the suitability of Powerbase for any purpose. We cannot be held responsible for any loss or damage due to the use of this software. All enquiries, requests for upgrades etc, should be directed to: Powerbase Support 112, Keighley Road Colne Lancashire BB8 0PH (01282) 866835 email quercus@ukgateway.net website www.pendle.ukgateway.net/ When requesting an upgrade by post please enclose four first-class stamps to cover cost of disc and postage. Starting and ending a work session 1.2.1 Loading Powerbase Double-click on the Powerbase icon in a directory window and the program will load and place its icon on the iconbar. The words No data will appear under the icon indicating that no database is open yet. 1.2.2 Opening a database A database may be opened for use by either of the following methods: Dragging the database icon onto the Powerbase icon on the iconbar. Double-clicking on the database icon. This is the more usual method and may be used even if Powerbase is not already loaded, as long as it has been by the filer. What happens next depends on whether the database is password protected or not. If no password s have been defined (as is the case with all the sample databases on the distribution disc) the main record window will open and the name of the database will appear under the Powerbase icon. password -protected database will display the Access window in the middle of the screen requesting you to enter a password and perhaps even a personal user I.D. as well. The window has an button which should be clicked with SELECT when you have entered the required information. There is also a Cancel button which removes the window from the screen without further action. Passwords and I.D.s are case-sensitive and, if incorrectly entered, result in either a Password not known Access denied message. After three successive incorrect entries Powerbase will close down and remove itself from the iconbar. 1.2.3 Closing a database There are three ways of closing a database: Open another database, either by double-clicking or dragging to the Powerbase icon. Choose from Powerbase's iconbar menu. This also quits Powerbase itself. Choose Close database from the iconbar menu. This still leaves the Powerbase application running. The wording underneath reverts to No data If you have made any change to the database while it was open the disc will be automatically updated so that your changes are not lost, whichever method of closure is used. 1.2.4 Quitting Powerbase Choose from the iconbar menu. You need not specifically close your database first; any changes made to it will be saved before Powerbase quits. Obtaining help 1.3.1 Using the Acorn application When you are first becoming acquainted with Powerbase you may find it useful to load Acorn application. Powerbase iconbar Information window and main menu Sub le control window each have a blue button (like the familiar tourist information sign) which will launch the application for you. Resize the Help window, if you wish, and move it to some convenient place on the screen. Moving the mouse pointer over the various windows, icons and many of the menus displayed by Powerbase will then give a brief description of the purpose of each. 1.3.2 Using the Helpreader text Further help is available from the option which appears on both the main menu and the iconbar menu. Choosing this option displays a text file which you can navigate by means of small orange buttons included in the text. Clicking with SELECT on such a button takes you directly to the part of the text referred to. Double-clicking with ADJUST takes you back to where you were when you last clicked on a button. Clicking MENU over the text window displays a menu giving an alternative means of access to specific parts of the text. The text has deliberately been kept fairly short and is in no way a substitute for this manual; it is intended to provide basic information only. 1.3.3 Emailing Powerbase Support and accessing our website The iconbar Information window has buttons called and Email. Provided that the filer has your browser, clicking on the button will load it (if it s not already loaded) and insert the correct URL for our website from which you can download the latest version of Powerbase , its documentation, and a set of sample databases and script files. Similarly, if the filer has seen your mail program, clicking on the Email button will bring up a blank email with the correct email address for Powerbase Support. e-cli 0H} - There is one circumstance where this is not possible and that is when you are running a database from the RAM disc . Switching off the computer or quitting the RAM disc will, of course, result in loss of data. When you close a database on the RAM disc therefore, Powerbase will warn you to back it up to a more permanent medium. Thanks are due to Ben Summers who wrote the Helpreader application used to create and display this text file and who kindly gave permission for it to be distributed with Powerbase Ch 2 Browsing and Editing Opening a database displays the record window which shows the data for one record at a time. Each item of data in the record occupies a rectangle called a field Most (but not all) fields will have a descriptor or label which appears next to the field. In addition data fields will have a of up to four characters. Tags are very important (far more so than descriptors) as a means of identifying fields (see 4.2.4 ). If you click MENU over a field its tag is displayed in the menu, e.g. Field: NAME. Subfiles, record numbers and keys Look at the title-bar of the record window. Three pieces of information are displayed; the subfile number , the record number and the of the record. Since we will make frequent reference to these terms it will be as well to explain each one at the outset. 2.1.1 Subfile Suppose you have a bookshelf, capable of holding 100 books, and six partitions, numbered 0 5, which you can use to divide it into sections. The partitions are, to begin with, all stacked together at the left-hand end of the shelf. You may leave them like this if you wish and fill up the shelf with an unbroken run of 100 books. If, however, the books fall into well defined categories you might prefer to use the partitions to divide the shelf up into sections. There are no restrictions on where you can place the partitions, or how many books you put into each section, as long as you don t exceed the total of 100 books. In a similar way every Powerbase database is able to use up to six subfiles numbered 0-5. When you open the database you are looking at subfile 0 and you might have no occasion to bother with the others at all, in which case they can be ignored. Like the bookshelf partitions subfiles are there for you to use or not use depending on what your requirements are. The database remains the same size whether you use one subfile or all six and the number of records in a subfile is limited only by the defined size of the database. Unwanted sub les can, however, be disabled. To do so choose le control from the main menu. The window which opens has a button for each sub le and an associated menu button which allows them to be disabled and re-enabled. It is also possible from this menu to name sub les and to swap the positions of any two sub Section 2.2.5 suggests instances in which you might find it advantageous to use several subfiles. 2.1.2 Record number Every record in the database has a number which specifies its position in a file called Database . As you step through the records (see ) you will probably find that the record numbers shown in the title-bar look quite random. Record numbers reflect the order in which records were originally entered into the database, not the order in which you see them while browsing. The latter is determined by the 2.1.3 A key is a group of letters or other characters derived from one or more of the data fields in a record and used to identify that record. A record is, of course, uniquely identified by its record number but record numbers are of limited use because they only reflect the order in which the records were entered. Keys, on the other hand, are derived from the actual data and are stored in a special table called an index . The keys in the index are ordered , either alphabetically or numerically and can therefore be used to examine the records in an order which is related to the data. To use the bookshelf analogy again, you can specify a book as the 15th from the left (which is equivalent to using the record number) but specifying it by title, author or subject is much more useful. A key enables us to do the same for database records. A database may have several indexes built from keys based on different fields in the record but there be at least one which we call the primary key index . When you come to create a database of your own you will find that Powerbase insists that you define the primary key before you can use the database. Subsidiary keys may be defined at any time; whether you use them or not is up to you. Look at the field and at the key itself in the title-bar and you will see that the two are related. You can recognize the key field for the currently-active key by its descriptor (label) which will be red. Key fields for unselected keys have blue descriptors . Non-key fields have black descriptors. primary key field is further identified by the background colour of the data icon which is pale yellow instead of white like the other data fields. Moving about the database Attached to the left-hand edge of the record window is the tool-pane which contains buttons for moving around the database, adding and deleting records, searching for records etc. (An alternative way of presenting these buttons the keypad is described in 14.1.1 . It is also possible to place some or all of the buttons on the record window itself and use neither tool-pane nor keypad. 4.2.13 explains how to do this and the sample database Boxes shows such buttons in use.) Most of the buttons have keystroke equivalents provided by the numbered function keys F11) and experienced users might want to use these instead of the mouse. The relevant keystrokes are given in the text where appropriate but note that these are default values and may be redefined if so desired. Many other actions have equivalent Ctrl sequences which can t be redefined. See Appendix B for a full list. 2.2.1 Browsing At the top-left of the tool-pane is the single-step button This, when clicked with SELECT, steps forward through the records one at a time. Clicking with ADJUST steps back one at a time. The keystroke equivalents for these two actions are respectively. Beneath the single-step button is what we might call the fast forward/rewind button : it steps through the records in jumps of 10, using SELECT and ADJUST in the way just described. (Keys: Shift F2/F1 ). You can alter the number of records jumped by entering a different number in the writable icon to the right of the button. At top-right is the ends button : SELECT displays the final record, ADJUST displays the first record ( Ctrl F2/F1 The terms forward final and first in the preceding description refer to the order of records as determined by the in the index . The file wraps around so after the final record, SELECT-clicking the single-step button takes you to the first record. If you display the first record, ADJUST-clicking the single-step button displays the final record. The buttons on the 3rd row resemble the stop button s of a CD player or VCR and it will be convenient to refer to them by these names. Play (F11 ) causes Powerbase to run through the records, displaying each one briefly, and stop (Shift F11 ) halts the process. Closing the record window has the same effect as and so does the Escape key. If play is clicked with SELECT records are displayed in forward order. Using ADJUST displays them in reverse order. You may swap the order at any time by a further click on . The time for which each record remains on screen may be altered by changing the Fast forward interval (see above); the larger number you type into this icon the longer each record will remain on screen. At the bottom of the tool-pane are four bookmarks which let you mark specific records for rapid retrieval. Click on one with SELECT and the number of the current record appears in the icon. Clicking with SELECT on a bookmark icon which contains a record number will return you to that record. Click with ADJUST to clear the marker. 2.2.2 Cross-referencing records If you Shift/double-click on a field with SELECT, and the contents of the field match the current key of another record, that record will be displayed. The same is true if the field contains a record number preceded by a # symbol. This is a very handy facility for jumping quickly between records which are related in some way. It will also work if a field contains a comma separated list of keys or record numbers. A Shift/double-click on any one of these keys takes you to the relevant record. You could experiment with this using the Elements database. Look up, say, SODIUM, enter in one of the Notes fields the primary keys of the other Group 1 metals, i.e. lith,pota,rubi,caes,fran (case of letters is unimportant). You can now access the records of these elements from SODIUM s record. 2.2.3 Changing keys Skip the next three rows for now and examine the one bearing a blue symbol resembling a key. It is used to cycle through the available indexes using either SELECT or ADJUST (for reverse order, as you might expect). Shift F5/F4 have the same effect. Wrap-around again applies. If no subsidiary indexes have been defined the button will have no effect. 2.2.4 Changing subfiles Underneath the key-change button is one for changing the subfile being examined. A newly-opened database always displays subfile 0. Clicking SELECT on the subfile-change button ) causes subfile 1 to be accessed instead. Repeated clicks advance the subfile number by 1 until 5 is reached, after which wrap-around to subfile 0 occurs. ADJUST, as you might guess, accesses the subfiles in reverse order ( ). Sub les which are disabled (see 2.1.1 ) are skipped over and cannot be accessed. 2.2.5 Using more than one subfile Powerbase database consists, as stated earlier, of six subfiles numbered 0-5. You may use subfile 0 (which is the one used by default) and ignore the others if you wish, but there are occasions when users might find multiple subfiles very useful. If, for example, you are maintaining a set of pupil records in a school you could assign one subfile to each year. At the end of a school year the promotion of Year 1 pupils to Year 2, Year 2 to Year 3 etc. can be brought about very easily using the Rotate sub feature . You will nd the two action buttons for this on the window accessed from le control on the main menu It actually renumbers the subfiles so that (for clockwise rotation) subfile 0 becomes 1, 1 becomes 2 etc and 5 becomes 0. Records which were previously in, for example, subfile 2 will then be found in subfile 3. In an 11-16 school this would put the leavers into subfile 0, from which they could later be removed. You will be asked to confirm that it s what you want before Powerbase proceeds. A more common use of subfiles is to use subfile 0 to hold the in-use records and subfile 1 as a repository for records which you want out of the way but don t want to lose permanently. Powerbase is actually used in this way in a certain Further Education college where the applications file for a course is built up in subfile 0. On enrolment day records are updated to reflect students subject choices and all the records for applicants who have not appeared are moved into subfile 1. The enrolled students in subfile 0 are then timetabled and class lists are printed. During the first week of the course there is a trickle of late enrolments which simply involve retrieving the required records from subfile 1 and reinstating them in subfile 0. The facilities for moving records, singly or in batches, from one subfile to another are described in 2.4.2 2.5.6 . You can, of course, also select a subfile and type new records directly into it. 2.2.6 Naming subfiles To give subfiles meaningful names choose le control from the main menu . This opens a window with pop-up menus for each sub then leads to a writable menu item into which you may type a name of up to 20 characters which becomes the name of the relevant subfile and will be saved on closing the database. Searching for a record s all very well being able to move about the database like flipping through the pages of a book but how do we find a specific record? Powerbase provides three ways of doing this: you can search for the record by , by record number or by using a filter 2.3.1 Searching by key The button with the question mark is the search button ). When clicked with SELECT the Search window opens to the right of the tool-pane. Type the key of the required record into the writable icon and click on the Find button or type Return. To see how the key is constructed from the key field click MENU over the record window and choose Index => Show details Ctrl K ). This displays a window describing the key structure of the currently-active index. If the key exists in the index the record will be found and displayed. If the key doesn t exist Powerbase beeps and displays the nearest matching record with the key field flashing . If the database is a small one with which differ greatly from record to record then the displayed record may not look to be very near at all. If you typed Return or used SELECT on the Find button the Search window will close after retrieving the record. If you want to search repeatedly it is more convenient to have the window stay on screen and this can be achieved by clicking on Find with ADJUST. If you do this you will notice that the icon above the Find button displays the number of matches found Clicking on the icons (the up and down arrowheads) in the Search window allows you to search an index other than the current one. The index used affects the search only; clicking the browsing buttons on the tool-pane will show you that the current index has not been changed. A search carried out as described looks only at the current subfile. If you suspect that the record you require might be lurking in some other subfile you can force all six to be searched . This can be achieved by using ADJUST ( Shift F8 ) instead of SELECT on the search button. When the Search window appears you will see that the subfiles radio button is selected instead of This subfile . The radio buttons enable you to change your mind after the window has been opened. searching on a numeric key you must enter a number of exactly the right value. If, however, the key is alphabetic you may use just the first few letters if they are sufficient to distinguish the key from others. There are two other buttons on the Search window restores the last key entered into the writable icon and Cancel simply removes the window from the screen without further action. 2.3.2 Searching by record number You may type a record number, preceded by # (e.g. #1054), in the Search window instead of a key. Provided the record is in use Powerbase will find and display it, regardless of which subfile it is in. Bear in mind that, if the record is found in some other subfile than the current one, that subfile will become the current one. If the record has been deleted or has never been assigned a Record #xxx is not in use message is displayed. 2.3.3 Searching by filter Filter button ( Ctrl F8 ) is just above the bookmarks on the tool-pane. Selecting it brings up the Filter window which contains a writable icon into which you can type a search formula (see ). You should then click on the Filter button in the Filter window or, alternatively, type Return. The buttons used for browsing will now only display records which match the search formula , all non-matching records being filter ed out. Deselecting the Filter button closes the Filter window and restores normal browsing operations. So does clicking Cancel on the Filter window. The Close button simply closes the Filter window but leaves the filter in effect useful if you want the window out of the way once you ve set up a filter. Clicking on the single-step button with the filter window open and a filter active causes a displayed number to increment with every match found until the end of the subfile is reached. Wrap-around then occurs and the number no longer increases. Clicking Count displays the total number of matches in the subfile straight away without the need to step through the records. Editing the database fundamental operations 2.4.1 Adding new records The icon bearing a plus sign is the add record button ). Clicking with SELECT displays a blank record for you to fill in. The subfile number and anticipated record number are shown in the title-bar of the record window but there is, of course, no key as yet because there is no data. The title-bar therefore reads (New) in the place where the key would normally appear. In most databases you may leave blank any fields except the primary key field. Powerbase does, however, allow other fields to be defined as mandatory fields (see 4.2.14 If the database contains such fields (which normally have red as their foreground colour) you will be told about them when you try to move to another record or close the database and will be unable to do so until you fill them in. You might also find that on some fields pressing certain keys has no effect. This will happen, for example, if you try to type letters into a field which has been defined as Numeric. Ctrl D will insert the current date into a field and Ctrl T will insert the time. The fields need not be designated as Date or Time fields for this to work (if so designated they will accept the date or time) but the data will not be inserted if it is too long for the field. If you type into a field whose text and border are dark green you may get an error message. These fields only accept certain values which are listed in a validation table (see ). To see what these values are make sure the caret is in the relevant field then click on the Table button on the tool-pane. There are times when this data-validation feature can become a nuisance, so it can be turned off. Bring up Powerbase s iconbar menu and choose Preferences. One of the option buttons on the left of the preferences window is called Validate input. Deselect this button and click on Accept. All validation, including insistence on filling in mandatory fields, will then be disabled. Ctrl F9 can also be used to toggle validation on and off. After entering data into a field you can go to the next field in sequence by typing either the Return or the down-arrow key . To go back one field use the up-arrow key . These arrow keys work in such a way that attempting to go beyond the last field of the record takes you to the first field and attempting to go back from the first field takes you to the last our old friend wrap-around again. Typing Return on the last field of a record acts rather differently in that it writes the record to disc and brings up a new blank record for editing, i.e. it duplicates the action of the add record button on the tool-pane. You can also place the caret in any editable field by clicking with SELECT. When you have entered as much of a record as you wish you can click add record again for another blank record. The record you have just entered is automatically written to the database. The same is true if you click on any other tool-pane button or close the database altogether. You don t need to use the Update ) button to tell Powerbase to write the record its real purpose is to make Powerbase save indexes and validation tables which are stored in memory. Occasional use of the button during a long working session guards against power cuts or system failure. 2.4.2 Deleting records The two buttons below the search and buttons are concerned with record deletion. The second of these, which suggests putting an index card in a dustbin, is obviously a delete button Ctrl F10 ) but what about the other? A click with SELECT ( ) moves the displayed record forward, normally by one subfile, e.g. if the record is in subfile 0 it will be moved to subfile 1. If it is in subfile 5 it will be moved into subfile 0 ( wrap-around again). Clicking with ADJUST ( ) normally moves the record back one subfile. A record in subfile 5 will be moved into subfile 0. Normal behaviour is modi ed, however, if sub les have been disabled (see 2.1.1 ). The record is then moved to the next (or previous) enabled This button, which we will refer to as the move record button , clearly doesn t delete records at all, except in the sense delete from the current subfile . We often want to get a record out of our way but keep open the option of bringing it back again and that s where these buttons are really useful. If you have a lot of records in subfile 0 and want to do a clear-out you can use subfile 1 as a dumping-ground for the unwanted records. You re not really deleting them: just removing them from the subfile you re working in. It is sometimes useful when moving a record, to be able to follow it into its new subfile. You can do so by holding down Shift while you click on the move record button. e.g. If you use Shift-SELECT to move a record from subfile 0 to 1 you will be left with 1 as the current subfile and the record displayed. delete (dustbin) button should only be used when you are sure you want to lose the record permanently. As this is quite a drastic action Powerbase will ask you to confirm it before the record actually disappears. Nervous users may prefer to leave this button strictly alone and do all their deleting with move record . If this eventually results in a lot of clutter in the subfile next to the one you re using you can always do an occasional clear-out as described in 2.5.6 2.4.3 Altering existing records A displayed record may be altered manually without any restrictions other than those applying to the addition of new records (see 2.4.1 ). Alterations which would result in a change to the primary key must be confirmed. Normal RISC OS conventions for editing writable icons apply, e.g. Ctrl U clears the icon. As with new record entries you need do nothing to save your changes to disc. The process takes place automatically before a different record is displayed, when a database is closed or on quitting Powerbase 2.4.4 Undoing mistakes Changes to the record on-screen can be undone provided you haven t clicked on Update (not normally needed anyway) or done anything to cause Powerbase to display a different record. In other words, you can undo changes made to the current record as long as it remains on the screen. You may either the alterations made to the record or only those relating to specific fields. If you bring up the main menu and choose changes the record will be returned to the state it was in when you displayed it and before you began to edit it. To restore an individual field, click MENU over the field then go to the Field submenu and choose changes from there. Only ordinary writable fields and check-boxes may be restored; you can t restore External field s (see ) which is why a warning is normally issued before deleting such fields. Editing the database special features Section described all the editing facilities you actually need in order to maintain your database. The features described in this section are not essential but they can save you time and make your use of the database more efficient. 2.5.1 Using a template You may want certain fields to be filled in with fixed contents on every new record to save typing the same thing every time and Powerbase provides a means of doing this. Call up the main menu, go to the Miscellaneous submenu and choose Edit template Ctrl E ). A blank record is displayed and the window title says Enter data which you wish to appear by default on new records . Type in the required fixed data then click any of the usual tool-pane buttons. You will now find that the new record displayed every time you click add record is no longer blank but already contains the data entered in your template. This feature affects records only; it doesn t have a retrospective effect on existing records. 2.5.2 Copying fields The two buttons above the Update button may be thought of as copy to clipboard Ctrl C ) and paste from clipboard Ctrl V ) respectively. They can save a lot of time when you want to copy data from one field into another. When you click SELECT on the contents of the field which owns the caret is stored in memory. Positioning the caret in another field and clicking on paste will copy the stored data into the new field, erasing the field s previous contents (if any). If the data is too long for the field it will be truncated. If there is nothing on the clipboard the data from the same field in the previously-displayed record will be copied to the field. copy paste are not limited to copying into another field of the same record; you may use them to copy data between records or from a record into a validation table or a dialogue box or, indeed, between any two writable icons within Powerbase . Data remains on the clipboard until you use copy again. Even more conveniently data can be copied by simply dragging with SELECT. If you place the pointer over a field and hold down SELECT, a text-file icon appears under the pointer. This may be dragged and dropped into another field (provided it is an editable field) or into a writable icon in a dialogue box. It can even be dragged to another database if you have two copies of Powerbase running at once with two databases open. (The field contents may also be dragged to a different application such as a wordprocessor, or to a filer window where it will be saved as a file named from the field . See also This is a suitable place to explain how Powerbase can also copy data to and from other applications by means of the global clipboard . The latter is a feature of RISC OS which few users seem to know about and not all applications use it. Here s how it works. Highlight one or more fields with ADJUST as described above and make sure the caret is in the displayed record. It needn t be in one of the highlighted fields; any field will do. Now type Ctrl Shift C . Nothing appears to have happened but the highlighted data is now on the global clipboard. If you open, say, an Impression window, place the caret in it and type Ctrl V the data will be copied to Impression If, on the other hand, you highlight data in Impression and type Ctrl C , then place the caret in a Powerbase record and type Ctrl Shift V the data will be copied from Impression Powerbase . The same method may be used to copy data from Powerbase Writer, Easywriter Techwriter. StrongEd you need to type Ctrl Shift V . Although copying in the reverse direction works fine with Impression it does not appear to work with these other applications. doesn t appear to support the global clipboard at all. 2.5.3 Copying an entire record When a new record contains much of the same information as an existing one you can save time by copying a whole record and editing the copy. Display the record you want to copy and then click the add record button with ADJUST instead of SELECT ( Shift F10 ). No blank record appears when you do this; instead an exact copy of the current record is displayed and a message appears informing you of this. If you make no changes to the copy Powerbase will add the new record to the database. (It is assumed that no-one will ever want two absolutely identical records in the same database!) Any alteration, however, leads to the record becoming part of the database. 2.5.4 Choosing the field where editing starts When an existing or blank record is displayed Powerbase places the caret in the first (i.e. lowest-numbered) writable field of the record. (This might not necessarily look like the first if the fields have been placed in out-of-sequence positions in the window.) When going through a database making changes to one or two fields somewhere in the middle of a large number of records it might be convenient to have the caret start in another eld. Click MENU over the field where you wish editing to start, go to the Field submenu and choose Start editing . When a record is displayed the caret will be placed in the chosen field. The same procedure is used to restore the normal starting position. This result can also be achieved by placing the caret where you want and typing Ctrl @ 2.5.5 Changing many records at once To alter a given field in a set of records point the mouse at the field and click MENU. Go to the Field submenu and choose Global changes . In the first writable icon ( Replace ) enter what you want changing and in the second ( ) enter what you want the first string changing to. In the third you may enter a search formula (see ) specifying which records are to be changed. Finally click on Change or type Return. You will be asked for confirmation before the changes are allowed to take place. Note the following: Only records in the current subfile are affected. Replacement only occurs if the new data will fit in the field. Leaving Replace blank causes the target field to be replaced in any record which matches the search formula , otherwise replacement occurs only where the existing field matches the Replace string. Leaving blank causes the target field to be blanked in records which match the search formula and in which the target field matches the Replace string. Leaving the search formula blank causes replacement to occur in all records of the current subfile for which the target field matches the Replace string. It should be apparent from the above that leaving both Replace Search formula blank will place the string in all records of the current subfile. The characters and may be used as wild-card characters in the Replace and strings. The first of these is used to represent a group of characters which don t need to be matched. If you enter: Replace $Street with $Road the substitution will be performed on any record which matches the search formula and where the target field with Street . The ending will be changed to leaving the rest of the string unchanged. Another example of use is: Replace with 1047$ If the target field begins with , the beginning will be changed to , the remainder left unaltered: Replace $MPUT$ with $NFUS$ would change occurring anywhere in the target field into , e.g. COMPUTER would become CONFUSER (!). You may, of course, use in the middle of a word: Replace re$ment with dis$d would cause a string in the target field beginning with and ending with to have its head and tail replaced with and , leaving the middle unchanged. e.g. replacement would become displaced character is used to represent a single wild-card character and can replace individual letters in specific positions. The Replace with strings here must be the same length and should have characters in the same positions. The characters in the remaining positions are the ones which get replaced, but only if they occur in the corresponding positions of the existing string. You may also use Global changes to perform simple calculations e.g. will add 12 to the current value in the field on all the matching records. *5-13 will multiply the current value by 5 and then subtract 13. It is operations like these which are most likely to produce values which won t fit in the field, especially if floating-point numbers are involved. The operation isn t limited to Numeric field s, or even to other types of field consisting wholly of numerals; Powerbase will find the number embedded in an entry such as ABC45XYZ and the operation would convert it to ABC57XYZ It is also possible to change the case of letters in a field, without any other change to the data, by placing special strings in the field. These strings are distinguished by being placed in square brackets: Make all letters uppercase Make all letters lowercase Make first letter of field uppercase, the rest lowercase [ULUL] Make first letter of each word in field uppercase, the rest lowercase 2.5.6 Moving or deleting many records at once Choose Move/delete from the Miscellaneous submenu. This opens a window which gives you the choice of three types of operation: Delete records from a designated subfile Accumulate records from all subfiles into one designated subfile records from one designated subfile to another The default is to Move records from the current subfile to the next in sequence, but any source or destination subfile may be specified by clicking on the bump-icons. Type in a search formula (see ) and click the default action button or type Return. All matching records will be moved or deleted as specified. Operations may be undone by clicking on the button, but only for the last operation carried out. 2.5.7 Hiding sensitive data Your database might contain information which you don t want just anyone to read. A field may be hidden in the following way: From the iconbar Utilities submenu choose Alter format . This puts Powerbase into design mode . No data is displayed; just the empty fields over a blue grid. Double-click with SELECT over the field you wish to hide. In the Field design window select the option button and click Update field From the main menu choose Quit design . This returns you to normal editing mode The field you ve just doctored will now have vanished. Typing Ctrl Shift F1 makes it reappear, but with its text in mid-grey to show that there s something special about it. Ctrl Shift F2 will hide it again. Even if you close the database with the field visible it will always be hidden when the database is re-opened. The field can only be returned to normal using the procedure detailed above, deselecting the button. Whilst in the invisible state a field cannot be selected for printing (see ). ADJUST has no effect and you can t sneak round the protection by using Ctrl A to select all fields or by choosing a range containing the hidden field (i.e. by clicking with SELECT on an earlier field then double-clicking with ADJUST on a later one). Its tag doesn t appear in the Field entry of the main menu when you click MENU in the relevant place, nor does the field appear in the field list produced by typing Ctrl F . As soon as you reveal the data by typing Ctrl Shift F1 it can be printed as normal. If you intend to use this facility you are strongly urged to password-protect your database (see Ch 11 ). Only a user with Manager access rights will then be able to use Ctrl Shift F1 or access Alter format to remove the protection. If you leave the database unprotected then anyone can use the key combinations and might easily discover them by chance (or by reading this manual!). 2.5.8 Protecting data against deletion If you follow steps (a) and (b) above you will see beneath the button another option button called Selecting this button and returning to editing mode causes the field to be locked against alterations. The Return and arrow keys skip over the field and an attempt to place the caret in it directly results in a message telling the user the field is locked against changes. External field The data in External field s (see 4.2.8 ) is not stored within the Database but in separate files, one for each record. With the exception of Remote fields these files are stored inside the database s application directory. They may be Text, Sprite or Draw files and can be linked to buttons on the record window. Clicking on these buttons loads the External file into Paint provided that the relevant program has been by the filer. (For Remote fields the file objects can be of any type whatsoever including applications and directories and clicking on the button performs the appropriate action, e.g. an application is run, a directory is opened, an Obey or BASIC file is executed, other files are loaded into the appropriate editor if the filer knows where it is. Remote fields are discussed fully in It is also possible to link Text files to a Text block field which will actually display the text (with certain limitations) within a large icon. Sprites may be similarly displayed in a Picture field 2.6.1 Linking files to the fields To link a file with an External field drag the file s icon onto the record window and drop it on the appropriate field. For example, if the record includes a field represented by a Text button , i.e. one bearing a small version of the Edit icon, you may drop a Text file onto it. The button s icon changes to the small version of the Text file icon to indicate that the file has been linked. Sprite and Draw files are linked in the same way and Powerbase will not allow the wrong type of file to be linked. A Text block field also may have a Text file dropped onto it. The text (or as much of it as will fit inside the icon) is then displayed on the record window . The formatting will not be the same as it is when you examine the text in ; lines are word-wrapped and centred within the display icon. (This is a feature of multi-line RISC OS text icons and is out of the control of Powerbase .) You can t edit the text directly but it is possible to export the file for editing (see below). Sprite file s may be dropped onto and displayed in a Picture field. Any new file dropped onto an External field which is already linked to a file simply replaces the old file with the new. 2.6.2 Editing External fields You can t edit an External field directly, only via an appropriate editing program such as Paint . As long as the editor has been by the filer clicking on the on-screen button with SELECT will load the file into the editor and display it. You can edit the file and re-save it without altering its name and it will remain associated with the same field of the record. The contents of Text block and Picture fields are loaded into their editors by a double click. 2.6.3 Clearing and exporting field contents Clicking MENU over an External field makes available certain entries on the Field submenu which are shaded for other types of field. Remove external lets you delete the linked file from the database application. As deleting a file is irreversible you would probably like to be warned when this is about to happen so the message Delete object? Are you sure? normally appears. Finally, Save contents on the Field submenu lets you export the linked file to a filer window or to another application. 2.6.4 Editing scrollable lists Entering or altering data in a scrollable list is not very different from doing so in other types of field, but some keys do behave differently and there are extra keystrokes to access the special features of such lists. Return moves the caret from cell to cell across the current row, then jumps to the start of the next row. When it reaches the last visible cell the list will scroll up a row if there are still rows to display. If the last cell of the list has been reached the caret moves to the next field or next record, just as it does for other types of editable field. Shift-Up-arrow has the opposite effect to Return, i.e. it moves the caret to the previous cell. Shift-Return behaves like Return until the last cell of the list is reached. It then adds a row to the list, moving the caret to the start of the new row. You may add as many rows as you wish and each record can have a different number of rows. Insert with the caret anywhere in the list also adds a row to the end, but this time without scrolling the list or moving the caret. Ctrl-Insert will insert a blank row in the middle of a list, just above the row containing the caret. Shift-Insert is used to remove unwanted blank rows. This key-combination is quite safe: it always deletes the last row but only if the row is blank. The choice of the Insert key in this and the following combination may seem strange, but Delete can t be used because it already has a standard function in writable icons. Ctrl-Shift-Insert will force the deletion of the row containing the caret with the loss of the data from that row. You aren t very likely to use this accidentally. Neither Shift-Insert nor Ctrl-Shift-Insert will let you reduce the number of rows to fewer than the minimum the window can display: when the scroll-bar completely fills its slot this minimum has been reached. Up and Down arrows move the caret vertically from cell to cell, staying in the same column. When at the top or bottom of the list wrap-around occurs instead of the caret moving to the previous or following field as it does for other field types. Wiping a scrollable list Click MENU over the list and go to the Field submenu. Choose Blank list . You will be asked for confirmation before the data is deleted. Data export and import When MENU is clicked over a scrollable list two choices become available on the Field submenu in addition to Blank list noted above: Save as list saves the contents of the scrollable list as a plain text file. The contents of each cell of the list occupies a separate line and there is nothing in the file to indicate which items came from the same row and which came from different rows. If such a file is dropped onto a scrollable list the data will be imported cell by cell, working along each row to the end then moving to the next. Save as CSV saves the list as a CSV file (see ) with each row of the list as a separate CSV record and each cell in the row as a CSV field. The characteristics of the file are determined by the CSV Options window just as they are for exporting data from ordinary fields. If you drop such a CSV file onto a scrollable list field the result is somewhat different from the importing of a text list as described above because the data in the file is more structured; the row and column information of the field from which the data came has been retained. Suppose you have exported such a file from a 3-column scrollable list. If you drop the file onto a 2-column list the third item in each line of the file will be ignored: the destination field will contain what the first two columns of the source field contained. If, on the other hand, you drop the file onto a 4-column scrollable list the 4th column of the list will be left blank. Backing up Clicking the Backup button on the tool-pane with SELECT will back up your database to a user-de ned directory. If no directory has been speci ed (or if you wish to change the backup options) click the button with ADJUST. A window will open on which the desired backup directory should be dropped. Its pathname will then appear in the writable icon. You may choose to keep 1, 2 or 3 generations of backup and Powerbase will move backed-up copies into subdirectories Older and Oldest where required. An option button may be selected to force automatic backing up whenever the database is closed. You can save these choices in the database or in Powerbase itself and an icon to the right of the pathname displays to indicate where choices are stored. There is also a button which opens the backup directory for inspection. ackup 0 k - 0 * - 0PK - * -P@ Colours used to identify key fields are the default colours but it is possible for the user to change them (see The tool-pane """""" If the primary key is derived from two or more fields you needn t fill in all of them provided the result isn t a null key. The characters used to represent single and multiple wild-card characters may be changed via the Preferences window (see 14.5.2 * If you drop a text file in the wrong place in the record window Powerbase will think you want to treat the file as CSV file and import data from it (see CSV files ) and the Import text file window will appear. This is rather startling but harmless enough as long as you don t click on Import ! Click on Cancel to get rid of the window. Unlinking objects from Remote, Run file and Directory button elds is also done this way (see 4.2.8 and 4.2.13 Ch 3 Printing from the Database This chapter describes how to print out selected data from a database, including the printing of individual records and of labels. Such a print-out is commonly referred to as a report Output destination Printed output may be displayed in a window, directed to a specified text file or sent straight to the printer. Choosing Options from the Print submenu (keystroke equivalent Ctrl Print ) displays the Print Options window and you will see at the top of the window three radio buttons, Window Text file and Printer , which let you select the output destination . Descriptions of these options follow. 3.1.1 Window destination This is the default setting and, as its name suggests, it displays the completed report in its own window. Clicking with MENU over this window opens the Report menu which offers five choices: Save as text produces a Save box from which an icon may be dragged to a filer window or to any application which can accept a text file. The supplied pathname uses an appropriate leaf-name for the file and points to a directory called PrintJobs which is inside the database directory. Each database has its own PrintJobs directory which can be opened by choosing Show jobs done ) from the Print submenu. This menu choice also has its own submenu listing the directory contents. To save files with the least bother simply click on or type Return. You can, of course, delete the pathname ( Ctrl U ), type in your own filename and drag the file icon to any open directory. You might also be able to produce hard copy by saving the text to the Printers icon on the iconbar, although some recent printers contain no fonts of their own and therefore cannot print text files in this way. Look in PrintJobs regularly and get rid of files which you no longer need. (See also 3.1.2 will sort the report on whatever column the mouse pointer was over when you clicked MENU, provide the report is in Horizontal format (see 3.2.1 . The default sort order is ascending , i.e. from A-Z if alphabetic, from lowest to highest if numeric, from earliest to latest if dates or times (provided the fields are designated as type Date or Time). A submenu allows the choice of a descending sort if required. After sorting the report header shows the sort field and type of sort. Shrink list will remove as much surplus white space as possible from between the columns of a report. This item might be shaded: white-space removal can be set to occur automatically before the report is displayed (in fact this is the default setting). If this is the case no further space can be removed and the menu choice is therefore made unavailable. Discard removes the report window from the screen and recovers the memory it occupied. You might find this useful if you run short of memory after creating a large report. Restore will put a sorted report back into the order it was in when first generated. 3.1.2 Reloading saved reports Reports saved from the report window to the PrintJobs directory may be reloaded. Unless PrintJobs is empty, Show jobs done on the Print submenu will itself have a submenu. Choosing a saved report from this menu using SELECT will reload the report and display it in exactly the same format, colours etc. as when it was first created. If in Horizontal format the report may be sorted and records can be retrieved by double-clicking (see 3.1.3 For the above reconstitution of the report window to be possible it is necessary to save three files for each report. One of these is a plain text file which may be viewed in an editor, printed out, incorporated into a wordprocessor document etc. The other two are data files with the same name as the text file stored in subdirectories called RecNums and Tabs. If either of these ancillary files is missing it will not be possible to display the report as it was and it will probably be simply loaded into your text editor instead. This is what will also happen with text files which get saved in PrintJobs Powerbase functions other than normal database reports. To avoid leaving unwanted data files in RecNums and when deleting a report it is best if deletion is carried out from the Show jobs done submenu rather than directly from a filer window. To do so, choose the reports to be deleted from the submenu using ADJUST. The menu will remain open and the chosen reports will be ticked. To untick a report click again with adjust. Delete ticked will only be unshaded when at least one report is ticked. Choosing Delete ticked will then delete all the ticked reports, removing all three files pertaining to each report. Delete all will delete the entire contents of the PrintJobs directory after requesting con rmation. An option button in the Preferences window, Re-load last report allows the last saved report to be auto-loaded on opening a database. This is deselected by default. To activate the facility you will need to select the button and save the Preferences file either in the database (in which case it is operative for that database only) or in Powerbase , which will make it a default for all databases. 3.1.3 Calling up records from the report window The report window has one more useful feature. If you point at a particular piece of displayed data and double-click with SELECT the record window will come to the front displaying the relevant record. If the field corresponding to the item you clicked is editable the caret will be placed in that field. If you are using a report to look for errors in the data you can quickly correct them by this method but the report won t change to reflect your corrections until you re-create it. If you double-click with ADJUST instead of SELECT the record window opens at the pointer and its size and scrolling are adjusted to show only the required field. When a record has been retrieved by this method the relevant line in the report turns grey as a useful reminder. 3.1.4 Text file destination Save box will appear as soon as you tell Powerbase to go ahead and generate the report. The supplied pathname is the same as would be displayed when saving from the report window. Simply click to save it in PrintJobs under that name. Alternatively, type a filename and drag the icon to an open directory. The report will then be created and the file closed. Nothing else appears on the screen in this case. The File destination is of greatest use when reports are being produced from a Script file (see Ch 12 3.1.5 Printer destination When the Printer radio button is selected the button alongside becomes available. (It is shaded when Window Text file is selected.) Clicking opens the Printer Setup window which provides a wide range of options. You may specify the number of copies to print, choose the font and point-size to be used, print with the paper upright (portrait) or sideways (landscape) and set the inter-line spacing and any or all of the four margins. All measurements other than font size may be specified in mm (default), inches or points. If you are using a colour printer the colours for headers/footers, report body and rules, as specified in the Print options window, will be reproduced. Print formats Powerbase is capable of producing reports in four different formats. Only two of these, Horizontal and Vertical , are available when you print to a window or a file and these are selected via two radio buttons on the Print Options window. When the output destination is Printer two additional formats, Table and Label , are also available and are selected from the Printer Setup window. These four formats will now be described. 3.2.1 Horizontal This is so-called because each record appears on a single, horizontal line with the fields aligned so that they form neat columns. Non-numeric fields are left-justified. Numeric fields, and others whose content is treated as a numeric value (see 3.5.2 ), are right-justified . A header line identifies the columns by means of the tags or descriptors of the fields and this header can be made to appear on every page of the report or on the first page only. An optional descriptive title may also be incorporated. The Spacer is used to separate columns (see 3.10.2 ). All these features (and others) are chosen from the Print Options window. Besides the ability to sort a report in a window (see 3.1.1 ) you may force Powerbase to sort the report before displaying it. To do so, select the Sort on option button in the Print Options window and enter in the associated writable icon either the tag of the field on which you wish to sort or the column number of the report. The feature is only of use in Horizontal and Table formats, but may be used with the Printer destination as well as with Window . When the Sort on button is selected, another button next to the writable icon becomes active. Clicking on this toggles the blue arrow to point up or down, specifying either an ascending or a descending sort 3.2.2 Vertical Fields are printed underneath each other with the identifier (tag or descriptor ) at the left hand side. Where the field selection includes an External text file, i.e a Text or Text block field, the Vertical format is the only one which may be used and will be selected automatically. Although each field normally occupies a line to itself you can override this by holding down Shift as you click with ADJUST to select the field. You will then get a new line after the field: the next field to be selected will appear (with its identifier) on the same line. We will call this function field concatenation It may be applied to any number of fields: keep Shift down while selecting all except the last one to appear on the line. This feature is very useful if your report contains a mixture of long fields which need a line each and short ones which don t and would otherwise result in wasted space and paper. Concatenated fields are separated by the Spacer and the width of the report is governed by Text width (see 3.10.2 3.2.3 Table This, together with the Label format, is only available when outputting to the printer and the options associated with both formats are therefore selected from the Printer setup window. It resembles Horizontal format but the lines and columns are separated by horizontal and vertical rules, forming a grid. When this format is selected a number of extra features are enabled allowing you to include extra (blank) columns and lines, making this format especially useful when you want a list to which information is to be added by hand (e.g. entering marks against a printed list of students). The number and width of blank columns and the number of extra lines may be specified. For a tidy result it is recommended that you increase the line-spacing from the default 120% to about 150% when using Table format. 3.2.4 Label This is meant for printing on special label stationery. Since such stationery is expensive you are advised to try out your settings on plain paper first! Selecting this format enables the label setup choices which include the label size and the number of labels in a row. It also allows optional fixed starting and finishing lines to appear on each label. The number of lines on the label is not needed: Powerbase works this out from the label height and print size and warns you if the data won t all fit. Printing will normally begin on the first label in the first row on the sheet but, to enable you to use up a part sheet of labels , you may specify which label to begin with, e.g. for three-in-a-row labels, entering 5 would make printing start at the second label of the second row. (Remember that sheets in a feeder-hopper are upside-down!) After the first sheet the starting-point reverts to the first label in the first row. Each field normally appears on a separate line but fields may be concatenated (see 3.2.2 ) with the Spacer being used to separate the fields ( see 3.10.2 ). This may be necessary if you are using separate fields for surname and initials or surname and forename. You may specify one field to be substituted for another if the latter is blank. Both fields are specified by tag and the Substitute button is set. This is useful in a school or college situation where labels are being addressed to parents. Mature students, for whom the parent field in the record is blank, can have their own names printed instead. Another button makes the primary key of the record appear in small print on each label as a means of identification. This can be useful if the data printed on the label doesn t make it obvious which record it comes from. What types of field can be printed? Powerbase can use many different types of field. All are described in 4.2.5 4.2.13 in connection with setting up a new database and you should refer to those sections to clarify what follows here. You can print data from the following types of field Editable field (i.e.one into which you can type directly), including Scrollable lists Computed and Stamp fields (except Logos). External fields of and Text block type, and the pathnames in Remote fields. Check-boxes . What is printed for these differs from what you see in the check-box. Thus: (a) Tick/Cross boxes result in (b) Tick/Blank boxes, Option buttons and Radio buttons result in (c) Star/Blank boxes result in an asterisk or (d) Blank/Tick/Cross boxes result in (e) ?/Tick/Cross boxes result in (f) Blank/M/F boxes result in Female Note that (d), (e) and (f) are three-state check-boxes ; (a), (b) and (c) are two-state check-boxes . It is, of course, possible to print Draw and Sprite fields from individual records by loading the external file into Draw Paint and printing from that application (See 2.6.2 Specifying which fields to print The field or group of fields selected for printing is called a print selection. Point at each of the required fields and click with ADJUST. The fields will be highlighted by reversing the foreground and background colours. Only those fields which are printable (see ) will respond to ADJUST in this way. A second click will de-select the field. Note that the order in which you select the fields is important since that is the order in which they will appear in the report. The menu button at the bottom-centre of the Match window (see ) will list the fields in the order in which they have been selected. Ctrl F has the same effect. (If no fields are selected Ctrl F gives a listing of the fields.) contiguous range of fields may be selected by placing the caret in the first field then double-clicking with ADJUST in the last. To select printable fields choose Select all (Ctrl A) from the Print submenu. There is also a Clear selection entry on this submenu ( Ctrl Z Although a Scrollable list is, strictly speaking, a single field, its columns are selected for printing individually. You will find, however, that the order in which the columns are highlighted is immaterial; they are always printed in the order in which they appear in the record window. For other options applicable to printing Scrollable lists see 3.10.1 3.4.1 Saving print selection files Print selections may be saved for future use. Save selection from the Print submenu leads to a Save box. Accepting the default pathname will save the file with the name Selection in a directory called PrintRes . Just as every database has its PrintJobs directory, so does it also have its own PrintRes (i.e. Print Resources ) directory whose contents can be displayed with Show resources (Ctrl R) from the Print submenu. . Selection files are of type &7f3 and are distinguished by their icon which bears a large . Because they have a specific file-type which Powerbase recognizes they can be loaded by double-clicking on them. If PrintRes t empty then Show resources will have its own submenu which provides another means of loading files. 3.4.2 Default selection You may save as many print selections as you like and their names may include any character which is allowed in file-names, but one name is special: a file saved as !Selection is treated as a default selection . When you instruct Powerbase to create a report without having first highlighted the fields to be included, the default selection will be searched for in PrintRes and used. If there is no such file the primary key field(s) will be printed. As soon as the report is complete the selection is cleared. You won t see the highlighting of the fields at all when a selection is used automatically in this way. To save a default selection you need only select the option button Default selection in the Save box (thus causing the to be added) and accept the supplied pathname by clicking or typing Return. Specifying which records to print Unless we want to print all the records in the database we need some means of telling Powerbase what are the common features of the records we wish to print. There are two ways of doing this. The more versatile way (and the one which Powerbase uses by default) makes use of a search formula query describing the characteristics of the required records. The remainder of this section deals with the construction and use of search formulae. For the alternative method, query by example , see Section If you click the Print button on the tool-pane a small window with the title Find matching records appears. We will call this the Match window . The same thing happens if you choose Print from the main menu, or Create report Print submenu, or type the Print key on the keyboard. The most prominent feature of the Match window is a group of icons enclosed by a thin red border. This object is called the Query panel and you may have already seen it since it forms part of several windows. It appears on the Filter window for example (see 2.3.3 ) and is also used when making Global changes 2.5.5 ), performing a Batch move/delete operation ( 2.5.6 ), exporting a CSV file ( ) and creating a Subset ( The writable icon in the Query panel , in whatever context the latter appears, is meant to take a search formula . The simplest thing you can do, of course, is to type nothing at all! If you then click on the Print button you will create a list of all the records in the current subfile of the database. You could achieve the same result by typing indeed if, after producing the above list with a null formula, you click on the button Ctrl O) , which retrieves the last-used search formula , you will find ALL displayed. 3.5.1 The construction of search formula Most database queries will involve a selected group of records. A search formula describes the criteria which records must match in order to be included in the report. Getting to grips with search formula e is, perhaps, the biggest hurdle faced by the new Powerbase user and you are referred first to the simple examples described in the Tutorial file. A search formula consists of one or more search element search element specifies that a field value must fit a certain condition and takes the form: <TAG LIST>< COMPARATOR ><TARGET LIST> The angle brackets are there for clarity and are not used in entering the actual formula. There must be no spaces between the three parts. A tag list (if it contains more than a single tag) has the form: tag1,tag2,tag3,.... where tag1 etc. are field (see 4.2.4 ) which uniquely identify the fields to be matched. A target list (if it contains more than a single target) has the form:- target1,target2,target3,.... where target1 etc. are the data items which are to be compared with the contents of the fields specified in the tag list. The comparator which links the tag list and target list determines the type of comparison to be made. The commonest is , meaning that one of the items in the target list must exactly match the content of one of the fields in the tag list. If the Case button on the Query panel is selected then all comparisons will be case-specific, e.g. will be regarded as different from . If the Case button is not selected all those three will be considered identical. The heading of a report shows which fields were used in the search formula, what targets were specified and what type of comparison was made. If a target was placed in quotes (which is the only way of searching for any string containing a comma, for example) then it appears in quotes in the heading also. It is impossible to describe the use of search formula e adequately without quoting actual examples. As in the Tutorial file we will make considerable use of the Elements sample database. A simple example of a search formula consisting of a single search element is: where GP is the field tag, = is the comparator and T is the target. This means The field whose tag is GP must contain the value T , i.e. all transition elements (but no others) are to be included in the report. A slightly more complex one is: GP=1,2,3 which would be interpreted as The GP field must match one of 1,2 or 3 . This may also be entered as: GP=1 OR GP=2 OR GP=3 which is possibly easier to understand but also somewhat longer. A further example is: OX1,OX2,OX3=3 meaning One of the first three oxidation state fields must have the value 3 This could also be entered as: OX1=3 OR OX2=3 OR OX3=3 Yet another way is: OX1-OX3=3 i.e. you may specify a range of adjacent fields by giving the first and last separated by a hyphen . Where you don t know which fields to test you can replace the tag, tag list or tag range with @ , which causes all the fields in the record to be examined. Note that in these examples only of the fields in the tag list is required to match of the targets in the target list (although it doesn t matter if more than one field matches more than one target). Sometimes we want an inclusive search so that of the fields in the tag list match a given target or, less frequently, a field contains of the values in the target list. It s a matter of connecting the search elements with AND instead of OR. You can do exactly that (although the following example is chemically nonsensical!): OX1=3 AND OX2=3 AND OX3=3 You may also save typing by using the ampersand (&) instead of the word AND, but the same result can be achieved even more briefly by simply doubling the comparator , in other words using instead of so that the formula becomes: OX1,OX2,OX3==3 The full list of available comparators =, <>, <, >, <=, >=, { and (N.B. may be used instead of should be read as contains and as does not contain . These are used where the target value must (or must not) be part of the field but isn t expected to make up the whole field. The use of { is the main source of those rare instances where we want the items in a target list to be matched in a given field. e.g. Suppose we knew that someone s house number was 17 and that they lived on <something> Avenue but the actual name couldn t be remembered. In a database of addresses a search formula such as: ADDR{{17,Avenue (note the doubled comparator) would find it by listing all records where ADDR contained both 17 Avenue, whereas: ADDR{17,Avenue would find all those addresses where the house number was 17, regardless of street name, and all those addresses with Avenue in them, whatever the house number. You may invert the logic of a search criterion by putting in front of it. To print all non-transition elements you could use: NOT (GP=T) Note the space after , the need for brackets, and that the syntax GP NOT=T . You could equally well use one of the following: GP<>T GP~T and may find either of these more understandable. To make multi-criterion searches either place and targets in comma-separated lists as described above or string search elements together with the connectives AND and OR. Use AND (or the ampersand, &) when a field must meet of a set of criteria. e.g. GP=T & Z>50 & NAME{IUM for all transition metals with atomic numbers greater than and names containing . Use when a field need meet only of a set of criteria. e.g. GP=L OR GP=A would find all lanthanide and actinide elements as the formula means either L or A ; I don t care which . AND and OR can produce ambiguous search formulae e.g. GP=1 OR GP=2 AND Z<50 could mean either elements in group 1 or 2 (don t care which) with atomic numbers less than 50 or group 1 elements (of any atomic number) or group 2 elements whose atomic numbers are less than 50 You probably want the former, but Powerbase will give you the latter. To get what you require use brackets to make the logic clear. In other words write it as: (GP=1 OR GP=2) AND Z<50 You could also write this as: GP=1,2 & Z<50 without any need for brackets at all. 3.5.2 Numeric and other special fields in search formulae For most types of field the comparison with the target is made by character matching but for certain types the comparison uses the numeric value of the field . The fields concerned are: Numeric, Calculated, Record number, Sequence number, Day of month, Month number, Year Only the first of these is an editable field type but all may be included in search formulae. Suppose we have a Numeric field whose tag is NUM. If you entered the formu NUM=5 the record would be included in the report if NUM contained 5, 05, 5.0 etc. because all of these have the same numeric value. If you had an Alphanumeric field called NUM the same search formula would only match records where the content was literally 5, i.e. the character . This can easily catch you out. Suppose, for example, you want to print records for which NUM<8. You might be surprised to find records in which NUM contains values such as 55, 20, or 13 being printed, as well as those containing 4, 6, 2 etc! If this happens check what type of field NUM is. Unrestricted and Alphanumeric fields will give the above result; Numeric fields (and the others listed above) will give the result you probably want. You can force comparison by numeric value for a field which consists of (or, at least, begins with) numerals, even though the field is not defined as of Numeric type, by enclosing the field tag in square brackets, e.g. [NUM]<8 would produce the desired result in the above example even if the field is Alphanumeric or Unrestricted. This is useful where you want to make a comparison but still allow the field to accept non-numeric characters. The comparison-by-value can only work in such cases if the number part of the field comes first. e.g. it will deal correctly with 55A, 20B, 13X but not with A55, B20, X13. 3.5.3 Using wild-cards search formula The use of characters and wild-cards was described in 2.5.5 in connection with search-and-replace operations. They may be also be used in search formula is used to represent a group of characters and to represent single characters which do not need to be matched. e.g. If (still using the Elements database) you type: NAME=$ON you are, in effect, saying find all the elements whose names end in ON ; I don t care what precedes ON as long as nothing follows it Powerbase will duly find CARBON, BORON, NEON etc. If you were to use: NAME=$ON$ You would find PLUTONIUM, POLONIUM and a few others but none CARBON, BORON, NEON etc. Something must follow ON as well as precede it. (To print both sets of elements you would use NAME{ON Note also that: NAME=$TIN$ finds PROTOACTINIUM, PLATINUM etc, but TIN itself. NAME=S$IUM finds all names which begin with and end with , e.g. SAMARIUM, SCANDIUM and SODIUM. The effect of: NAME=S####IUM is somewhat different. You are, again, asking for names which begin with S and end with IUM but this time SAMARIUM and SCANDIUM would be found, but SODIUM since you have specified exactly 4 wild-card ed letters between the S and the I. Finally, to find any 5-letter name, regardless of the actual letters: NAME=##### 3.5.4 Querying scrollable lists If search elements are formulated in exactly the same way as for other fields, the entire list is examined for a possible match. e.g. If the tag of a Scrollable list is LIST entering: LIST=<target> will search every cell of the list and report the record as a match if any one or more cells contain the target string. The test can be restricted to a single column of the list by appending the column number as follows: LIST#2=<target> which would search cells in the second column only. Data in Scrollable lists is always of string type, even when it consists wholly of numerals. A comparison such as: LIST#2>15 will therefore fail to give the desired result. The trick of placing the tag in square brackets (see 3.5.2 ) will work, however. The following formula will find records in which a cell in column 2 has numeric value>15: [LIST#2]>15 3.5.5 Comparing the contents of two fields A field tag (instead of a literal string) may be specified as a target, thus allowing two fields in a record to be compared to produce, for example, a list of all records in which the relevant fields have the same content. This might interfere with a normal query where the required literal target happens to be the same as the tag of another field. The problem can be overcome by enclosing the literal string in quotes. 3.5.6 Saving search formulae for re-use Choosing Save query from the Print submenu opens a Save box from which the search formula may be saved. By default the file is saved in PrintRes under the name Query . Selecting the Save as default button on the Save box will cause the file to be saved as the default query with the name !Query . If such a file exists in PrintRes it will be automatically entered in the Query panel whenever the Match window is opened. A default query file, in other words, behaves in a similar way to a default selection file as described in 3.4.2 Query files are of type &7f4 and are recognizable by the large in their icon. You may save as many Query files as you like and load them into the query panel by double-clicking on them. Query by example After that lengthy description of the search formula method of querying the database we turn to the alternative: query by example. For brevity when comparing the two we will refer to them as and respectively. To select query by example choose Preferences from the iconbar menu, select the option button Query by example and click on Accept . The option then becomes active for operations which would otherwise involve typing a search formula into the Query panel. 3.6.1 What is The user is presented with a blank record and invited to type into the relevant fields the data which must be matched in order for the record to be included in the report. What you are saying in effect is: I want a list of all records which look like this. I don t care what s in any of the fields I haven t filled in, but the ones I filled in must correspond to what I have typed. e.g. in the Elements database if you wanted to print a list of all transition metals you would simply enter in the Group field and then click Print on the Match window . The of the field isn't needed at all, whereas using a search formula requires you to type . Although you can t normally place the caret in Computed elds and Stamp elds you nd that you can do so when using the blank QBE record. Check boxes will initially display an (for ignore ) and any check-box left in this state will be ignored in the query. Clicking a check-box when setting up the query removes the and displays the normal sprites (tick, cross etc.). Check-boxes so treated do gure in the query. Enter the data to be matched then either click with SELECT on the Print button of the Match window or else type the Print key. If you simply enter the required target strings Powerbase assumes that you want the all relevant fields to match exactly , i.e the effect is the same as using in every search element (see 3.5.1 ) of a search formula, connecting the search elements with AND. There are, however, other comparators besides which may be used in search formulae. (see 3.5.1 for explanation and complete list). You may use any of these in a QBE query by placing them at the start of the string, e.g. {Avenue in an Address field would match all records where the field contained the word Avenue . An address such as 15 Acacia Avenue could be found by this method whereas just entering the word Avenue wouldn t work because it would require the field to read Avenue and nothing more. Wildcards may be used; e.g. you could print from the Elements database all elements ending in IUM by entering in the field or all those whose symbol began with H by entering in the field. You may specify a target list (see 3.5.1 ) to make the search include all records matching any item in the list. e.g. Leeds,Liverpool,Manchester in a Town field (if it will fit) would cause records with any of these places to be included. You can also specify a field list (equivalent to a tag list ; see 3.5.1 ) provided that the fields form a contiguous group. The target string (which may be a target list, be wild-carded or be preceded by a comparator) is entered in the first field of the group. Press Return and enter " (double quote or ditto mark) in the next field and for the remaining fields of the group. (Pressing Return rather than moving the caret by means of the mouse ensures that you really are dealing with a contiguous group of fields.) 3.6.2 QBE vs QSF So what are the advantages and disadvantages? QBE is very intuitive and avoids the need to bother with field tags or the minutiae of search formula syntax. On the other hand QSF is more comprehensive and flexible: there are some things you simply cannot do with QBE. Some of the limitations have already been mentioned but here is a complete list: Except where exact matches ( field=target string ) are required it might not be possible to fit the comparator-plus-target string or list into the field. You can't, for example, target a Date field for all dates prior to, say, 01-06-90 since dates fit their fields exactly leaving no room for the necessary < comparator. Such a search is perfectly possible with QSF. Searches involving multiple fields and the same target, i.e. those where FieldX FieldY FieldZ must match are only possible where the fields form a contiguous sequence. With QSF it is possible to perform such searches on fields dotted about the record. There is no equivalent in QBE to the @= ( field in the record matches) or @{ ( field contains) searches which are possible with QSF. You can't search for text in an external Text or Text Block field. You can with QSF. Since QBE doesn't use tags you can t force a comparison-by-numeric-value on a non-numeric field (See 3.5.2 If your requirements involve only fairly simple searches then QBE might be just what you ve been looking for, but if complex multiple searches are often needed then QSF is the one to go for. It is, of course, a simple matter to switch between the two. As supplied Powerbase uses QSF as the default query method and the Query by example button will be deselected when the Preferences window is displayed. If you want to make QBE the default you can save your Preferences le either in the database (in which case only that database will use QBE by default) or in Powerbase itself to make all databases use QBE. Other features of the Query panel and Match window buttons on the Query panel have already been dealt with. Selecting Reverse causes whatever index is in use to be scanned in reverse order, e.g. alphabetical lists will be produced in Z-A order. opens the Help window which offers another way of building search formula e which might appeal to beginners. Select the target field by cycling through the with the bump icons or by choosing from the pop-up menu. Choose the comparator by selecting a radio button. Type the target value into the writable icon. Place the caret in the Query panel writable icon and click Add to formula. The search element will appear at the caret. You may click on and enter other search elements in the same way. If you wish to use button you must do so before clicking Add to formula Powerbase inserts the brackets round the search element for you. Holding down Ctrl and clicking on a field with SELECT while the caret is in the Query panel causes the tag of the field to be entered in the search formula at the caret. This, together with the above method of constructing search formula e, largely overcomes the problem (especially when using someone else s database) of not remembering what the field are. At the far left of the Match window is a group of four radio buttons labelled Print Count and Clear . Only one of these may be selected at a time and the default action button at the bottom right of the window reflects whichever one you select. When the Match window is opened it is always Print which is selected, this being the most often used feature. If you merely want to know how many records match a specified set of criteria, without printing them, select Count . The number of matching records appears to the left of the Cancel button (which merely closes the window). and Clear are explained in 3.8.2 below. Fields selected menu button will be shaded if there is no field selection, otherwise it lists the selected fields in the order of selection . This last is well worth remembering since there is no other indication of the order in which fields were selected for printing. The icon just to the left indicates the selected output destination (see ) by displaying a representation of a window, a text-file icon, or a printer. In the latter case the icon will be shaded if no printer driver is loaded. Clicking with SELECT on the icon opens the Print Options window ; in fact you might find this the most convenient way of doing so. In many databases some keys may be repeated several times. This is especially true of subsidiary keys, but sometimes also occurs with primary keys. A report created with Ignore repetitions of key selected will contain only the first record having a given key; subsequent ones will be skipped. 3.7.1 Printing records from more than one subfile Reports are usually created from records in the currently-selected subfile which is displayed in the title bar of the record window. Just under the Query panel is the legend Include subfiles: and a row of numerals, 0-5. When a database is opened 0 will be highlighted, indicating that reports will only include records from subfile 0. If you change subfile by clicking on the appropriate tool-pane buttons you will see this highlighting move from one number to another, showing the selected subfile. You can, however, click on these numbers so that any or all of them are selected. Subfiles are deselected with a second click. When you create a report from more than one subfile the records are not merged into one alphabetically (or numerically) ordered list; the ordering starts afresh for each selected subfile. This isn t really a problem because you can always sort the completed report on any field to produce a single, ordered list (see 3.1.1 3.7.2 Including record number, key and subfile number A group of three option buttons in a frame to the left of the query panel allow you to include record numbers, the current key, and the subfile number in a report . The latter is especially useful if you have created a report from several subfiles and then sorted it as described above. If you need to keep track of which subfile a record comes from, create the report with selected. The position in which these items appear in a report depends on the point at which the option buttons are selected. Think of selecting these buttons as an extension of selecting data fields, e.g. selecting a data field, then selecting the button, then finally selecting another data field would lead to a report in which the key was printed between the data fields. The extra items appear in blue on the Fields selected menu and are saved as part of a Selection file. Marking records for inclusion or exclusion 3.8.1 Arbitrary selection of records There are times when you want to print a number of records which have no obvious connection with one another: they may have a common feature which is obvious to but none within the records themselves. Such a situation commonly occurs when you want to print a few mailing labels. No common feature means no basis for constructing a search formula. So how do you tell Powerbase which records you want to print? A small panel attached to the bottom of the record window contains a check-box, Mark for printing etc. , which you can tick to indicate that the displayed record is to be printed. If this panel is not present type Ctrl M , which toggles the feature on and off. With the default settings of Powerbase the mark panel is displayed but it can be turned off by an option in the Config file (see ). Even so, Ctrl M will always bring it back. Using the Search button or the browse controls you can call up each record you want and tick the box. You then simply select the required fields and print in the usual way. If no search formula has been entered then only the marked records will be printed. (Printing without a search formula when no records are marked gives the whole subfile as described in ) If you do enter a search formula you will get the records which match the formula the marked records, whether the latter match the formula or not. A pop-up menu allows you to invert the effect of this feature so that printing without a search formula gives all records in the subfile except the marked ones and printing with a search formula gives all the matching records except for those marked. When the menu option is set like this (to exclude rather than include) the check-box shows a red cross instead of a green tick. Next to the check-box is the Clear marks button which does exactly what it says. It is shaded when no record is marked. A further indication of whether records are marked is provided by the icon at the far right of the Query panel which displays either the green tick or the red cross when any record is marked. This applies to the whole database, by the way, not just to the current subfile. Powerbase takes heed of marked records in any operation which involves the query panel, i.e. batch move/delete, global change, filter, export subset, export CSV file, as well as print. 3.8.2 Groups of records It is sometimes useful to be able to mark (or clear marks from) a group of records which fit a search formula. This is made possible by the and Clear radio buttons on the Match window. By repeatedly selecting and executing different query operations you can build up a set of marked records by stages then, if desired, selectively clear the marks from certain ones. Finally, you can print your carefully-tailored selection of marked records without using a search formula at all. Printing only the displayed record Hold down Shift whilst clicking with SELECT on the Print button of the Match window or type Shift-Print on the keyboard. Yet another method is to mark the displayed record as described in 3.8.1 then do a Print ALL . Only the marked record will be printed. The highlighted fields of the displayed record are printed using the currently-selected print format as determined by the setting in the Print options window. If no fields are selected the action is as described in 3.4.2 Powerbase will use the default selection if it exists or, failing that, print the primary key fields only. print options window To display this window you can choose Options from the Print submenu, type Ctrl Print , or click SELECT on the icon to the left of the Fields selected menu on the Match window. Features such as Destination (see ), Format (see ) and the Sort on facility (see 3.2.1 ) have already been dealt with extensively. The rest are covered here. 3.10.1 Scrollable lists These may be printed in two different ways. The default is for all the cells in the selected columns of the list to be made into a single line . The entries in and Row end are the strings used to separate data from individual cells in the same row of the list and to separate one such row from another. The defaults are ,<sp> and ;<2sp> respectively but you may provide your own strings (up to 5 characters each) if you prefer. This format can result in very long lines indeed, especially if all the columns of multi-column lists are included in the print selection. An option button ( Shrink line - selected by default) causes as much white space as possible to be removed, but lines could still be too long for the printer. The alternative format puts the data from each row of the scroller on a separate line so that the data aligns in columns . This occupies less room horizontally but much much more vertically. is used between the data from cells in the same row but Row end is shaded because nothing is needed to delineate rows. It can be very inconvenient to have an entire list (or even all the data from a single selected column) included in a report when only a single cell is of interest. The option button Only if targetted in query (deselected by default) overcomes the problem by restricting what is printed to those rows in which the contents of a cell match a target in the search formula. 3.10.2 The rest of the print options The remaining options require only brief explanations. Default settings appear in brackets after the name of the feature. Headings (tags) appear at the head of reports in all formats except Label unless is selected. Expand codes (OFF) causes extra data from a validation table to be substituted for (or added to) the coded data in fields linked to such tables (see Expand headers (ON) will show the expanded versions (see ) of the target values for fields linked to validation tables in the list header. Turning the option OFF causes the target values to be shown exactly as typed in the search formula. Upper case (OFF) causes all textual output to appear in capital letters. Print header (ON) causes the printing of header lines at the beginning of each page. The header includes the following information: The name of the database plus a title, derived from the search formula , making it clear on what basis the records have been chosen. index used for the ordering, plus the date stamp (if appropriate button selected). An optional description entered in the Title writable icon column headings as described above. The next button (on p. 1 only: default OFF) limits the header to the first page of a report. Print footer (ON) Reports in Horizontal and Table format normally end with a footer which specifies the number of records printed. If the output includes Numeric or Check-box fields and column calculations have been selected (see ) the results of these too will be part of the footer Date stamp (ON) makes the date and time when the report was created appear as part of the header Shrink list (ON) Horizontal Table format the width of columns is determined by the maximum defined length of the fields included in the print selection. These lengths are often greater than the length of data actually present in the fields, resulting in a lot of white space between columns. With this option ON the surplus space will be automatically removed. Even if it is OFF you can still remove white space via the Report menu (see 3.1.1 ). Output to Printer always removes white space whether this button is ON or OFF Page numbers (OFF) allows page numbers to appear at the bottom of each page of a report. This feature works quite independently of the Print footer button. Page length (0) determines the total length of page, including header footer and top margin, for destinations other than Printer (for which the page length is determined by the top and bottom margin settings). The default value of 0 means no division into pages at all, but you might want to alter this if you drag text-files to the printer. An A4 page is 70 lines long, but you won t be able to print on them all and page-feeds might occur in the wrong place. Look at Edit paper sizes on the iconbar menu of Printers . Subtract the displayed top and bottom text margins from 70 and enter the resulting value. In Vertical format Powerbase will try to avoid splitting a record between pages, but this can happen if the report includes Text or Text Block fields of greatly varying length. (It will also happen if the number of fields to be printed exceeds the length of the page!) Text width (A) specifies the line length used when printing in Vertical format. means and lets the program calculate the value. You may enter your own value (e.g. 70) to override this. Colours (red, black, green respectively may be chosen for headers (also footers), the report body, and rules. Only foreground colours may be specified. This is done by clicking on the icons with SELECT to cycle through the 16 standard Wimp colours. (ADJUST cycles in the reverse direction.) The colours are used for reports sent to a Window or to the Printer Spacer (1) specifies how fields printed on the same line will be separated. Fields are first padded with spaces to the maximum width of the relevant data field (but see Shrink list above) and the spacer string is then printed before starting the next field. Four interpretations of the contents of this icon are possible: A number by itself means use the specified number of spaces A number followed by a non-numeric character means use a string of the specified number of that character, e.g. means 3 hyphens A wholly non-numeric string is normally used as is , e.g. but:- A string containing | or produces solid or dotted vertical rules between columns when outputting to Window or Printer in Horizontal format 3.10.3 Saving print options files All the settings in the Print Options and Printer Setup windows may be saved as a Print Options file. Clicking Save choices with the in database radio button selected brings up the familiar Save box. By default the file is saved in PrintRes under the name PrintOpts . As with Selection (see 3.4.1 ) and Query files (see 3.5.6 ) you can save a default options file called !PrintOpts by selecting the Default options button on the Save box and Powerbase will load this whenever the database is opened. Options files have a large on their icon and a filetype of &7f5. You may save as many as you wish and load them with a double-click. If the in Powerbase radio button is selected the options are saved as the Powerbase default and no Save box is displayed. Load default reloads this file, overwriting any changed settings. The printer setup window When the report destination is set to Printer clicking Lots more on the Print options window gives access to the Printer setup window. Some of the features of this window have already been described in connection with the Table ( 3.2.3 ) and Label ( 3.2.4 ) formats. Despite the complexity of the window most of the rest is fairly self-explanatory and only a few comments are necessary. 3.11.1 Line length and point size of fonts It is very easy to choose a combination of field-selection and point-size which makes it impossible to fit the entire line into the available width, especially if the paper is upright (portrait mode) and printing in two or more columns is selected. When Powerbase has prepared the first page of data for printing it looks to see whether the longest line will fit. If not, it calculates what the point-size would have to be reduced to in order to make it fit. If this results in an unfeasibly small size (less than 6pt) you will be advised of this and asked if you wish to proceed at the original point-size with some loss of data or cancel the job. (You will be told which field(s) will be omitted or truncated.) If the calculated new size is 6pt or greater you have three choices: use the new size, proceed with the old size (with data loss as described above) or cancel the job. If the print job is cancelled you should then look to reducing the number of fields printed or changing the print format. 3.11.2 Print margins There are places in the window for setting all four margins. If, however, you set a margin which is less than the minimum specified by the printer driver (which will probably mean as near the edge of the paper as the printer is capable of printing) then the printer driver s minimum is used instead. This particularly affects the minimum bottom margin which is quite large on many ink-jet printers; possibly 15mm or more. If you specify a bottom margin of 10mm and find you get one of 15mm it probably isn Powerbase fault! If a printer driver is loaded the As printer button will be available and all four of the printer driver s minimum margins will be used. 3.11.3 Printing speed Hard copy printing from Powerbase t wonderfully fast but you can help things along by choosing sensible options in !Printers . Some users run a high-resolution colour ink-jet printer at 1440dpi in a colour mode and never think to alter it. We re only printing text, when all s said and done 720dpi will produce excellent text quality and will be 4x as fast. 360dpi will be as fast as 1440! For rough draft work even 180dpi might be acceptable, if your driver allows such a setting. Many users are unaware that using the printer in a colour mode is much slower, even when printing black only , than in a monochrome mode. (The software has to look for colours which might be there, even though you know they aren t!) Finally, the use of a good printer-spooler application such as !FastSpool+ will cause control to be returned to the user that much more quickly and is highly recommended. Field analysis reports Field submenu has an Analyse option which allows you to print a breakdown of the field contents under certain special circumstances: (a) If the field is index the menu entry will read Analyse index . When chosen it will produce a list of all the values in the index with the number of times each one occurs. e.g. A database of college students might have a field for the school of origin. If there are 20 different schools and if the field is index ed then a list of those schools will be generated showing how many students came from each school. (b) If the field contains an 8 or 10 character date (e.g. 19-10-42 or 19-10-1942) the menu entry reads Analyse months and a breakdown by month will be printed. There will be a line for each month showing the number of records for that month. For example, this could be used in an orders database to find out quickly how many orders were received or dispatched each month. This feature works on editable Date field s and also on Date stamp 8 and Date stamp 10 fields in the Stamp class. It is, of course, possible for a field containing a date to be index ed. Action (a), above, takes precedence in such a case. You can, however, force action (b) instead by first selecting the field with ADJUST, then choosing from the menu. For cases not described above the menu entry simply says Analyse and is shaded. Printing is always to a window (from which the report may, of course, be saved); the Destination buttons in the Print options window have no effect. Subsidiary indexes and printing speed Try the following experiment using the sample database Elements Create a subsidiary index, if one doesn t already exist, on the Group field (see for the way to do this). make the index case-specific Enter the formula and click Print with ADJUST so that the Match window remains open. Note the time taken for creating the report. Deselect the button and repeat the process. The time will be shorter. You might also notice the brief appearance of a numeral (probably 1) in the small rectangle to the right of the search formula in the Query panel. The speed increase is most noticeable with a slow machine such as an A3000. On a StrongArm RiscPC and with such a small database both times will be so short that the user s reaction might be So what? but when dealing with databases of thousands of records the improvement can be quite dramatic. What happens is that Powerbase detects the fact that there is an index based on the Group (GP) field, goes straight to the first occurrence of in that index then prints records for as long as the key remains . The number briefly displayed in the Query panel is the number of the index being used and its appearance shows the user that a subsidiary index is being used. If you look at the header of the report you will see that it says Ordered by GP index whereas the first time it said Ordered by PrimaryKey . The speed-up only works when the all following conditions are met: The search formula must include a simple comparison for equality, such as TAG=target, without alternatives: no OR, no tag list, no target list. whole of the field represented by TAG must be indexed. button on the Query panel must be set to agree with the index, i.e. it must be selected if the index is case-specific and deselected if not. That s why the difference was produced by deselecting the button the index on GP isn t case-specific so the query mustn t be either. 0,I -P@ / -P@ 0he - 2 -w7 0p! - 0$7 -P@ o -P@ D - 7 0(3 - 0xj - * In this section we will often have occasion to refer to text typed by the user. Such text will be distinguished by the use of the Corpus Medium font, e.g. Ch 4 Creating a New Database Five steps are involved in setting up a new database: Create the database application shell Design the record layout Specify the number of records the database is to contain. Specify the primary key . Build the empty database Creating the database application shell You need to have Powerbase installed on the iconbar but with no database open, i.e. No data should appear under the icon. Click SELECT over the icon and a Save box will appear containing the default name !Database . Type in the name of your database and drag the icon to a directory window. Remember that, for RISC OS versions prior to 4.00, the name cannot exceed 10 characters, including the initial . If you enter more the name will be truncated and you could end up overwriting an existing database with a similar name. You don t actually need to enter the Powerbase will insert it automatically. If you are using RISC OS 4.xx on an E+ formatted hard-disc you will be able to use long filenames but, to do so in Powerbase, you will need to make a change in the !Powerbase.Resources.Config file. Look for the token NameLen and alter the number beside it to something bigger than 10. The change will take effect next time you run Powerbase. If you open the newly-created application directory (Shift double-click) you will find that it contains four files ( !Run, !Sprites, !Sprites22 and Data) and six directories ( Indexes Menus PrintJobs PrintRes , UserFuncs ValTables ). All databases expect these objects to be present so don t delete any of them. Designing the record layout When you created the application shell you were left with a window on screen, blank apart from a grid of blue lines, and it is here that you must design the database record. This is the lengthiest part of setting up a database, although efforts have been made to render it as easy as possible. If you have closed the window just click SELECT on the Powerbase icon on the iconbar to re-open it. 4.2.1 Simple field creation Clicking MENU over the window brings up the New database menu on which every item except Create field and Grid is shaded at this stage. Choosing Create field displays the Field definition window which lets you specify the characteristics of a field. Grid allows you to choose options for the grid used to lay out the fields. This grid appears only in design mode, not in a working database (except when using the Adjust format and New record format features). It may be turned off but you will probably find it a help in getting the field layout as you want it. There are options to choose the colour of the grid, whether it is represented by solid or dotted lines and what the spacing between the lines is. By default the grid has solid, light blue lines spaced 32 OS units apart and fields will to it at intervals of 4 OS units. If you prefer settings other than the defaults Save choices will ensure that they are preserved next time you load Powerbase . If you make further changes without saving them clicking Load defaults will recover your most recently-saved settings. You can return to Powerbase original settings by Shift-clicking on Load defaults First decide what class of field you want to create. There are eight such classes, selected via radio buttons: Editable Computed Scrollable list Stamp Check-box Tool-pane button (or Keypad button) External Extra button For the present we will confine ourselves to the first, which is the default selection. As well as the field class you must decide on the type of field within the class. The default offered is Unrestricted , meaning that it will accept all printable characters. We ll look at other types later. Decide on a name for the field and enter it in the Descriptor icon. This is the name which will appear on the record window and may be up to 40 characters long. You must also enter a , which is used to identify the field in search formula e and is limited to 4 characters. Next enter the Data length ; the maximum number of characters the field is to hold. Values up to 246 are allowed. Now click on Create and the field will appear on the record window s probably not where you want it so drag the white rectangle with SELECT to the position required. When you drop the field in its new position the descriptor falls into place too. If you want the descriptor somewhere other than to the left of the data icon (above it, for example) move it by itself after positioning the data icon. For fine adjustment re-open the Create window by double-clicking on the field and nudge the field into position using the bump icons at the bottom left corner. To edit an existing field you can either click MENU over the field and choose Edit field or simply double-click SELECT over the field itself as above. You can also display the data for any field by choosing from the Fields Created submenu. This is also available from a menu button on the Field definition window. After making changes click on Update Create will be shaded). 4.2.2 Deleting, inserting and re-ordering fields It is important to understand the difference between the physical position of fields on the screen and the internal numbering of the fields. The former is purely a matter of appearance and you may drag the fields about the screen to your heart s content, but the latter is fundamental to the way the database will function. Each field has a number which corresponds to the order in which it was created. Whenever you invoke the Field Definition window the title-bar shows either the number of the (new) field you are about to create (e.g. New field 3 ) or the number of the (existing) field you were pointing at when you opened the menu (e.g. Modify field 5 ). The numbering of fields determines the order in which they will be visited by the caret when you are actually using the database and typing Return to get from field to field, i.e. the editing order. A field may be deleted by bringing it up for editing as described above and clicking on Remove field . Fields which come after the deleted field will then be found to have had their field-numbers reduced by 1. Inserting a field into the middle of the existing field-sequence is obviously a little more complicated because we have to specify where in the sequence the new field goes. You need to know the number of the field which you want to follow the new one. This number is entered in the before icon before you click on Create . The layout of this part of the window should make things clear: Create before <n> . If you examine fields which come after the insertion you will see that their numbers have increased by 1. You can change the numbering of a field by removing it and then re-inserting it, but there is a better way. Bring the field up for editing as previously described. We will call this the current field . Enter the number of another existing field in the same place as was used above to specify the insertion position of a new field. We ll call this the entered field . You may then do one of the following: Click Swap with . This does exactly what you would expect. It swaps the positions of the current field and the entered field in the field sequence (but not their placement on-screen). Click Renumber as . This is a bit more complicated. The current field acquires the number of the entered field. If this involves giving the current field a lower number than previously the fields beyond the new position are all moved up one place to open a gap for it, at the same time closing up the gap left by moving the current field from its old position. If it is being given a higher number the fields above its old position all move down one place, closing up the gap left by its removal and opening a gap in the required place farther up the sequence. (Once again, the physical position of the fields on the window is unchanged.) 4.2.3 Moving and re-sizing the bounding box Left to itself Powerbase makes all its field icons the same height and of a suitable length to contain the number of characters specified in Data length . This may not be quite what you want. If the data length is large the field could run off the right edge of the window. You might also want to make the field taller for emphasis. (But for displaying multiple lines. Only fields of Text block type can be multi-line. These are discussed later.) We have already seen how you can alter the position of a field by dragging with SELECT. By dragging at an edge with ADJUST you can change the size of the bounding box. You can also specify both size and position by entering the required width and height of the bounding-box in the and icons and the co-ordinates of the lower-left corner in the and icons of the Field creation window All these values are in OS units (the same units as are used for plotting to the screen) but the origin is the left corner of the record window . This means that the Y values are always negative. Clicking on automatically sets the bounding-box width to fit the data length. 4.2.4 More about and descriptors Tags are very important Powerbase . They are used when querying the database to produce reports, export CSV files etc. and also by some internal operations. Every printable field (see ) must have a tag and no two tags can be the same. Descriptors are less important. They are there to provide visible labels for fields and in some cases you may not need one at all. e.g. You might want the record to look like this: There are 5 fields here but, having given the second one the descriptor ADDRESS, you don t really want descriptors for the remaining 3 (except perhaps POSTCODE for the last). It is quite in order to have null descriptors like this, but your must give each field a tag. Suitable ones might be NAME, ADD1, ADD2, ADD3, CODE. (Remember each must be unique and not more than 4 characters.) omit the tag where the Data length is 0 . This allows you to create fields which are simply explanatory labels. Since there is no data in them there would be no reason to include them in a query. 4.2.5 Other types of Editable field So far we have only used fields of Unrestricted type. Clicking on bump icons to the left of the field type, or on the menu button to the right, lets you cycle through the various types available. These are: Unrestricted Accepts any printable character. Alphanumeric Accepts all letters and numerals and common punctuation. Upper case Accepts capital letters and numerals only. Numeric Accepts numerals, +, and . (decimal point). Yes/No/Maybe Accepts Y, N and ? only. Date Accepts dates in the form dd-mm-yy or dd-mm-yyyy, checking that the date is valid and reporting an error otherwise. Accepts times in the form hh:mm:ss up to a value of 23:59:59. time is checked for validity and errors are reported. Internet Special field for email addresses and web URLs. There is no restriction on character input, but double-clicking will call up your mail program or browser (if it has been the filer) with the address loaded. When Numeric is selected, certain icons in the dialogue box which are normally shaded become available. Thus, you can specify floating-point, fixed-point or integer format by means of a group of radio buttons. You may also specify a maximum and minimum value for numbers which may be entered in these fields. The Numeric min icon is also used to hold the starting value for Sequence number fields (see 4.2.10 Date field should be either 8 or 10 characters long in order to hold the date in one of the two formats specified above. The hyphen separator in these dates may be changed via the Preferences window (see 14.1.2 ). Powerbase is very tolerant of the way you actually enter a date. You may type non-numeric character as a separator: Powerbase will make sense of an entry such as 4/5/87, converting it to 04-05-87 when you type Return. Arithmetic may be performed on dates held in this type of field, e.g. you could have a Calculated field subtract the contents of two Date fields and display the difference in days (see 6.1.5 Time fields also allow flexibility in how you enter the values. If you enter 3.45;9 it will be reformatted as 03:45:09. The colon separator may be changed via the Preferences window (see 14.1.2 ). You may also enter incomplete times which are, by default, interpreted as follows. A number entered on its own is treated as hours. Thus, if you enter 6 and type Return it will be reformatted as 06:00:00. Two number separated by a non-numeric character are treated as hours and minutes, e.g. 6/5 would be reformatted as 06:05:00. You may edit Powerbase s Config file to reverse this behaviour so that 6 is formatted as 00:00:06 and 6/5 as 00:05:06. When using this mode of entry you may also suppress the hours part of the display for values less than an hour, e.g. so that 6/5 is formatted as 05:06. (See for editing Config. Like Date fields, Time fields may be included in calculations, e.g. to obtain the difference in seconds between two times or to average a number of times (see 6.1.4 4.2.6 Scrollable lists These are also user-editable but are treated separately because they are like no other editable field. They are intended for record structures which contain closely-related data items whose number might vary widely from one record to another. A music CD, for example, might have only 3 or 4 tracks but it could have 30 or more. If you are cataloguing CDs and including track information you don t really want to define 30 separate fields to allow for just a few extreme records and find even then that the odd disc has more than 30 tracks! A scrollable list lets you make allowance for a modest number of items, adding new ones as required for individual records. Since the list is scrollable it occupies no more space on the record window no matter how many items are in the list. When the Scrollable list radio button is selected an extension appears at the bottom of the Field definition window. This contains writable icons to enter the number of rows of the list which will be visible and the number of characters to be accommodated in each column. The list may have from 1-4 columns. On clicking Create all that will be seen is a grey rectangle. Powerbase has calculated the height of this rectangle so that it will display exactly the number of rows you entered, and the width from an intelligent guess at how much room will be needed for the specified numbers of characters when rendered in the desktop font. It s best to leave the size of the rectangle alone, but move it to where you want it, of course. When the working database is created you will see the Scrollable list in all its glory. The data contained in Scrollable lists is not held in the Database file. Data for each record is in a separate file stored in a special system of subdirectories inside the database directory. In this they resemble External fields (see 4.2.8 4.2.7 Check-box fields These are fields whose status changes when clicked on with SELECT. Eight types are defined: Cross/tick Displays a cross initially. A click changes it to a tick. A second click changes it back to a cross. Null/tick Similar to (a), but initial state is an empty box. Null/star Similar to (b), but second state is a star. Option button Exactly like the square option buttons seen on dialogue boxes. Radio button Exactly like the round radio buttons seen on dialogue boxes Null/tick/cross This is a three state check-box the first state being an empty box. Repeated clicking cycles through tick, cross and back to empty box. ?/tick/cross Another three-state check-box where the first is a question mark. Null/Male/Female Yet another three-state box whose states are empty box, M and F. Check-box es provide the fastest way of entering true/false or yes/no type data and the 3-state types allow for yes/no/undecided situations. If you examine the file Powerbase.Resources. ValStrings you will find strings associated with each of these five types the latter parts of which read, respectively: QNo,Yes Q-,Yes Q-,Yes Q-,Yes QNo,Yes,- QNo,Yes,? QFemale,Male,- These specify what will actually appear in a print-out when a check-box field is included in a report (see ). You may change them if you wish, but don t omit the initial Q (although this will appear in the print-out) and take care not to alter other parts of the string. 4.2.8 External field External field s allow you to link Powerbase records to pieces of data of a size and type which make them unsuitable for inclusion in an Editable field. Such items are sometimes called BLOBs Binary Large OBjects ) in the PC world. The field types in this class and the types of data linked to them are as follows: Plain text files , such as Edit creates. Sprite Sprite files , such as Paint creates. Drawings such as Draw creates. Text block Plain text files, as for (a). Picture Sprite files, as for (b). Remote Anything! When you create a field of type Text, Sprite or Draw it appears on the record window as a button bearing a small version of the icon for Paint Draw respectively. Files of the appropriate type may be dropped on these buttons, whereupon the file is copied into a special system of subdirectories within the database application. Unlike fields of the Editable class the data doesn t become part of the Database within the application (see also 4.2.6 ); the Text, Sprite or Draw file retains its identity and may be exported for editing in the appropriate application. Clicking on the button in the record window will display the file if the filer knows the whereabouts of the relevant editor ( Paint Text block and Picture fields take things a step further by actually displaying a text or sprite file on the record window . The bounding box of the icon needs to be of suitable size to hold the text or sprite. In the case of a Text block too small a box will cause the text to appear truncated. None is actually lost; it just can t all be displayed. Too small a box for a Picture field will cause the sprite to spread beyond its boundaries. (N.B. To display the same sprite on each record, e.g. a company , define the field as of type , not Picture .) The contents of a Text block or Picture field can be loaded into Paint by double-clicking with SELECT. When the edited text or sprite is saved the Powerbase field will be seen to update. To break the link between the button and the External object choose Unlink file from the Field submenu. 4.2.9 Remote fields These demand a special section to themselves. They allow type of filer object (file, directory or application) to be linked to a button on the record window. They differ from Text, Sprite, and Draw buttons in that the linked object is copied into the database directory: the object remains in its original position in the filing system and only its pathname is stored to provide a link between database record and object. Hence the term Remote . This has both advantages and disadvantages. Because no copy is made disc space is saved and the database stays a manageable size; an important factor where large sprites or JPEGs are involved. A Remote button normally shows a large, down-pointing arrow inviting the user to drop an object onto it. It will accept files of any type, ordinary directories or applications. Once a link is established the button shows the appropriate file icon or a blue folder or the default application icon. A single click has the same effect as double-clicking the object in a filer window: directories are opened, applications are run, files are loaded into their applications, Obey files and BASIC programs are executed. If the linked object can t be found Powerbase will be aware that something used to be there and the button will display a question mark. Pathnames of Remote objects The stored pathnames may be either absolute relative , that is relative to the database . Which is used depends upon a setting in the Config file, which may be the file in !Powerbase.Resources or a private Config file stored inside the database directory. Look for the token Pathlen , beside which is a number (normally 255) followed by a letter: either . If the letter is omitted altogether (for elative pathnames) is assumed. Relative pathnames are only possible when the files are on the same disc as the database: otherwise Absolute pathnames are used even when not configured. Consider the file structure opposite. The directory called Pictures holds a collection of JPEG images (sub-divided into several categories), each image being associated via a Remote button with a record in the !MyPhotos database. The full pathname of the JPEG Horse ADFS::HardDisc4.$.FredsData.Pictures.Animals.Horse If you drop the JPEG file onto the Remote button and either the database or Powerbase itself is configured for bsolute pathnames, this is what will be stored. Everything is fine as long as the JPEG remains in the same directory on the same hard disc. But what happens if you decide to transfer your photo collection to CD ROM? The old pathname of the JPEG is no longer valid! Even if you transfer the whole of the above structure from the root directory down, the pathname will be different because the data is no longer on an ADFS disc called HardDisc4. On opening the !Photos database you will find the Remote button sporting a question mark instead of the JPEG icon. Clicking on the button is without effect: Powerbase t find the file on your CD because it still thinks the pathname should be the one beginning: ADFS::HardDisc4.$.FredsData. There is, however, a way of making this transfer to a different medium quite painless and that is to use elative pathnames. On the original hard disc the absolute pathname of the database was: ADFS::HardDisc4.$.FredsData.Databases.!MyPhotos If you compare this with the original pathname for Horse ll see that the first sections of both pathnames, as far as and including FredsData , are identical and this enables us to locate Horse without using its full pathname. Powerbase does, of course, know the full pathname for !MyPhotos when the database is open so it can find Horse by, in effect, saying: go up two levels in the disc tree from the database, then use: Pictures.Animals.Horse But how do we let Powerbase know it needs to go up two levels? The character , included as an element in a pathname, is an instruction to go up one level. What needs to be stored for the example given above therefore is: ^.^.Pictures.Animals.Horse which is the pathname relative !MyPhotos . When Powerbase reads this all it needs to do is prefix it with the already-known pathname for !MyPhotos and the JPEG Horse can be retrieved. If the directories Databases (containing !MyPhotos ) and Pictures (containing the subdirectory Animals ) were transferred to the root directory of a CD ROM named PICLIB the pathnames of Horse !MyPhotos would be: CDFS::PICLIB.$.Pictures.Animals.Horse and: CDFS::PICLIB.$.Databases.!MyPhotos If the relative pathname has been stored, as described above, it will still be valid: it s still a matter of going up two directories (thus reaching the root) and then working down from there. You don t, of course, have to work out the relative paths yourself! Powerbase does it all for you when you drop a file on a Remote button provided elative paths are configured. So which should you use; absolute or relative pathnames? There are advantages and disadvantages to both. Absolute pathnames are appropriate if the Remote objects are unlikely to be moved and, especially, if the same database is associated with objects on several different discs or filing systems. The database itself can be moved anywhere you like without affecting Powerbase ability to retrieve the data. Relative pathnames are more appropriate if the Remote objects are organised in a closely-related set of directories on one disc, especially if you intend to move the whole collection. It is, however, important to maintain the same relative positions between the objects and the database at all times. Relative pathnames may be globally converted to absolute ones using Miscellaneous=> Convert paths Where files are on the same disc as the database it is also possible to convert absolute to relative pathnames. Any which cannot be converted will be recorded in a file NoCanDo PrintJobs along with the record numbers. A window is displayed showing the configured setting (which is not changed by the conversion) and radio buttons to select the type of conversion required. The Query panel is also present enabling you to target specific groups of records and select the subfile(s) to be searched. Confirmation is required before conversion proceeds. A Remote button may be highlighted with ADJUST in order to include the stored pathnames in a report. You can examine the pathname of an object by clicking Shift-ADJUST on the button. 4.2.10 Auto-displaying External files The Field definition window has a Display button which is shaded for all except External fields. If you select this, and if an application for loading and displaying the file is known to the filer, then every time a record is displayed the associated External file will be displayed without the need to click on the button. This makes it possible, for example, to run a slide show of linked images by clicking the button on the tool-pane. Thomas Olsson s PD application EasyView is very fast at rendering JPEGs and doesn t open separate windows for successive JPEGs. This makes it very suitable for this type of application. You needn t decide at the field-creation stage whether you want auto-display or not: you can always set the option later. The feature may also be toggled on and off by clicking the button with Ctrl-ADJUST. 4.2.11 Computed fields Discussion of Computed fields is postponed to Ch 6 Performing Calculations. 4.2.12 Stamp field Stamp field s resemble Computed fields in that you cannot edit them; Powerbase stamps the fields with the appropriate contents automatically. The following types are available: Sequence number Unique value numbered from a base value set by the user. Record number Database record number. Time Time at which record was created. Date Date on which record was created. Date and time Date and time of record creation. Day (of week, month or year) on which record was created. Month Month in which record was created. Year Year when record was created. Last altered Records the date and time record is first created and updates it only if the record is subsequently altered. Merely displaying the record does cause updating. Allows a sprite to be included as a on every record. Frame Used to enclose groups of fields to clarify layout. These fields are stamped by Powerbase when a record is first entered and thereafter, with the exception of (i), stay fixed. (But see for similar types of field which auto-update.) Sequence numbers The base value from which sequence numbers begin is entered in the Numeric min box. Every time a new record is added the Sequence number is incremented. Be sure to set the field-length so that the biggest number envisaged can be accommodated. When a record containing such a field is deleted the sequence number is normally re-used; a new record is given a new sequence number. This leads to gaps in the numeric sequence and you might want to reassign the numbers so that the sequence is continuous. You can do so by means of Compact sequence from the Field submenu. Before this can be used the sequence number field must be indexed and selected as the current index. The base value of sequence numbers may be reassigned by choosing Set base value from the Field submenu. The new value must be larger than the largest sequence number currently in use. Record number requires no further comment. Time is in hh:mm:ss format. The colon separator may be changed in Preferences Dates can be in one of three formats: Date 8 displays the date as DD-MM-YY. The separator may be changed in Preferences. When such dates are used in calculations (see 6.1.5 ) years <=10 are treated as 21st century and others as 20th century. This turnover point may be altered by editing the number beside the CentChange token in the Config file. Date 10 displays the date as DD-MM-YYYY. Long date displays the date as (e.g.) Sun,01 Aug 1993 Date and time uses the format: Tue, 10 Aug 1999.21:18:36 (identical to the Real-time clock). day of week may be expressed as a numeral (1-7) or textually, either in short form (Mon, Tue etc.) or in full (Monday, Tuesday etc.). Day of month and Day of year are always numbers. Month may be a numeral (1-12), in short form (Jan, Feb etc.) or in full (January, February) etc. is always a 4-digit number. Last altered is displayed in the same format as Date-and-time Logos require the name of the sprite to be entered as the tag of the field to be used as a . This means that the sprite name is limited to four characters. The icon for a Logo field (like that of a Picture field) must be large enough to hold the intended sprite. You may have several fields on your record, all the required sprites being included in a sprite file called UsrSprites which is placed inside the database directory. Frames may be of either Channel Ridge type, both of which are familiar objects on RISC OS dialogue boxes. Be careful how you use them. You should define the frame before defining the Editable or Button fields which it encloses, otherwise you won t be able to get at these because they are behind the frame even though the latter is transparent. If a frame is to be given a heading this too needs to be defined after the frame or the channel/ridge will run through the middle of the text. Use a field of Unrestricted type with a descriptor only and a zero data length. This can be dragged to a position where it overlays part of the top of the frame. (You can t simply give the frame itself a descriptor and drag that into position because the descriptor has an earlier icon number than the frame and the frame will cut across it as described above.) 4.2.13 Button field Any or all of the control buttons on the Powerbase tool-pane keypad may be made to appear on the record window itself. They are chosen from the Tool-pane button menu and have exactly the same functions as they do on the tool-pane. These button field s let you build a customised database which allows the user to use only the features you want him/her to have access to since the tool-pane/ keypad and menus can then be suppressed (see ). There is also a group of Extra buttons which can appear on the record window: Print brings up the Match window for report printing. duplicates the action of Close database on the iconbar menu. Quit duplicates the action of on the iconbar menu. buttons are of two types. The first associates a specific data field with a pop-up menu . Clicking the button with SELECT and choosing a character string from the menu enters it into the data field, overwriting the previous contents. To associate the menu button and data field they are given the same tag: one of the very few cases where this is allowed. The menu data is stored in a text file whose name is the tag of the associated data field plus the word . When you click on the Menu button for the first time the text file is loaded for editing. The first line of the file reads UserMenu and you should change this to whatever title you want to use. If you leave it as UserMenu you will always be presented with the text file instead of an actual menu when you click SELECT on the button. Each menu choice must be placed on a separate line after the title. Save the file. The second type of menu button is not linked to a specific field and must be given a unique tag. It is a general menu button which may be used to insert strings into any Editable or Scrollable list field and is distinguished from the previously-described field menu button by being darker grey. In use, it inserts data into the field which contains the caret. Moreover, the data is inserted the caret without overwriting whatever is in the field already. All user-menu files are stored in the Menus subdirectory inside the database directory. To modify a menu just click on the Menu button with ADJUST, which loads the menu file for editing. User menus of the field type pop up automatically when the caret enters the associated data field. This behaviour can be disabled from the Preferences window. Directory In its initial state this button displays the small directory icon. Dropping a directory onto it changes this to a large directory icon and clicking on the button opens a filer window on the directory. This type of button does enable you to associate a different directory with each record: if you want to do that you need a Remote field. A Directory button is merely a convenient way of having a frequently-used directory always available, regardless of what record is displayed. The pathname of the directory may be absolute relative and is determined by the same Config option as for Remote fields (see 4.2.9 ). Two other Config options are relevant. DirOpts determines how the directory is displayed, the default being with small icons and alphabetic sorting. ButtonAtts enables the directory name to appear beneath the button. These features are fully described in .The link between button and directory may be broken in the way already described for External fields. Run file This type of button displays an icon made up of four different filetype icons. When you drop a file onto the button the icon changes to that of the relevant file and subsequent clicks on the button will run the file. Text files are treated as Powerbase scripts (see Ch 12 ), other types of file (e.g. Obey files) have their normal Run action. As in the case of the Directory button, this does not permit different files to be linked to different records; it s always the same file whatever record is displayed. Filenames are, again, either absolute or relative and the ButtonAtts option in Config applies to this button too. Unlinking the file is performed exactly as for the Directory button. Increment The simple task of increasing or decreasing a number in a field by a fixed amount can be a bit of nuisance to do manually, especially if several records are involved, so a button to do it with a single click is provided. Like the Menu button, the link to the data field (which must be Editable but need not be defined as Numeric) is supplied by giving it the same tag. Whatever you type in the Descriptor icon will appear as a legend on the button and, if you leave Descriptor blank, the default legend Increment will be used. The default increment is +1 and may be altered via Increment size on the Field submenu. Evaluate is set up in much the same way as Increment, i.e it is given the same tag as an Editable field and the descriptor is used as a legend (default is Evaluate ). The button is used to enter the result of a calculation into its associated data field and further discussion is postponed until 4.2.14 Mandatory fields This isn t yet another class of fields. A mandatory field is one which be filled in before you can access another record or close the database. It has already been noted that at least one of the primary key fields must not be blank (see 2.4.1 ), but any Editable field (but not Scrollable list) can be made mandatory by selecting the Must not be blank option button when designing the field. Such fields appear on a working database with red as the foreground colour. (This may be altered via the Colours window ; see A short-cut to a working database The third item on the New database menu is called Default database . Choosing this is by far the quickest way of getting a database up and running. Its action is to create three files inside the application directory. These are called Form (which holds the record design), PrimaryKey Database (which will ultimately contain the entered records). The number of records in the database is set to 100, with 25 as the amount by which this should increase when the database becomes full. The primary key is defined as the first four characters of the first Editable field . The database is opened and a blank record displayed ready for data entry. Since you can alter all the above characteristics later, you might wish to use these defaults while you experiment with the database. If you want to choose the database size and primary key structure yourself follow the procedure in Sections and Specifying the database size This involves two steps: Save the Form (which contains all the field data). The Save form file menu entry leads to a standard Save box but, since the pathname is correctly set for saving the file inside your database application, all you actually need do is click on the menu item itself. You will now see that the Database size choice is no longer shaded and may be used to reach the Size window in which you specify the number of records in the database and the increment for expanding the database when it becomes full. Specifying the primary key 4.5.1 General procedure Click MENU and choose Primary key This opens the Key Structure window. The primary key (or any other key) is derived from one or more record fields called key field s. Up to four key field s may be used to define a key but we will begin by using just one. Four pieces of information need to be specified: Choose the field , either by clicking on the bump icons or choosing from the associated pop-up menu. The default is the first Editable field in the record. Enter the within the field from which characters are to be taken to make up the key. The default is word 1. If 0 is entered word boundaries are ignored (see 4.5.2 Enter the position within the word from which characters are to be taken. L (default) means from start of word, R means take from end. A number ( ) means start at the th character. Enter the number of characters to be taken from the word. The initially-set value is 4 but thereafter the default is the defined field length. After entering this information click on Create and the empty Database PrimaryKey files will be created, after which the database will open ready for you to start entering records. 4.5.2 Some illustrative examples Key field s should be chosen with care. An ideal key field is one whose contents would never be repeated in another record. Powerbase lets you enforce this condition if you wish (see 11.2.1 ) but the default setting allows key duplication. Occasional repetitions may not be serious, but a field which can have only a few values is usually a poor choice. A customer number or membership number is the sort of thing we are looking for but your database may not contain anything like that. In a database of school pupils the pupil s name would be a good choice of key, but the form teacher s name would not, since only a small number of names would be involved, each appearing on the record of many pupils. Suppose you decide to use a person s name, stored surname first, in a single field whose tag is NAME, as a key. You could use the whole name but it would probably be too long. The first four letters would be more appropriate but, as this is the start of the surname, you might get a lot of duplication. e.g. PRESTON and PRESCOTT would both have the key PRES; and this is before we even consider people whose surnames are identical. To get round this problem, Powerbase lets you construct an alphanumeric key from characters taken from up to four successive words. You will seldom need to go so far. In the present case a five-letter key made up from the first four letters of the surname and one letter of the forename would be good enough for most purposes. Set up the Key Structure window as follows: Field Chars Position 1st row: 2nd row: This works quite well. Smith Peter and Smith Janet would have the SMITP and SMITJ. Duplication can still occur, but not often enough to be a serious problem. Further examples illustrate the use of letters from different parts of words in the key field. The following settings of position characters would produce the shown from the name Herring Albert. (a) (d) use the Surname only, (e) and (f) use the Forename only, the rest use both names: rom Surname From Forename Chars Chars HERRI RING (stops at word end) HERRALBE ALBING RINER GALBERT (ignores breaks) RINGAL (ignores breaks) Take particular note of what happens if the word number is entered as 0 (or left blank). All breaks between words are then ignored. The field is treated as if the blanks between words had been removed leaving a single long word which is then subjected to the process specified by the position and characters icons. When, on the other hand, the word number is 1 or greater the scanning for characters stops when the end of the word is reached so that the key might be shorter than the character length specified. Example (d) illustrates this. 4.5.3 Using more than one field in a key You might, of course, want to store surname and forename in different fields. Let s call their SNAM and FNAM. To produce the five-letter keys given in our first example the key definition would be: Field Chars Position 1st row: 2nd row: and the generated will be exactly the same as they would be for the single field NAME. There are databases where no single field is suitable for constructing the primary key . In a database of classical music, for example, there would probably be one field for the composer s name and one for the name of the work. Neither is much use individually; the former could contain many occurrences of Mozart or Beethoven and the latter many repetitions of Symphony No. 5 or String Quartet in D minor. This is the kind of situation where you might want to use two fields and as many as four words, taking one word from the Composer field and three from the Work field using, say, 4, 3, 3 and 2 characters respectively from them. The following work would then yield the key shown on the right: BEETHOVEN Symphony No. 5 BEETSYMNO.5 This is less than ideal: part of the key (NO.) conveys no useful information. The problem is even more acute in the following case: MOZART Piano concerto No. 23 MOZAPIACONNO The number is the and forms no part of the key: Mozart s piano concertos would have the same key! What we really want to do is ignore insignificant words like when constructing keys. We can do so by entering a list of the words to be ignored in the Ignore icon on the Key Structure window , using commas to separate the words. If we enter No.,A,The,the,of in the Ignore field the works mentioned above would be given the keys: BEETSYM5 MOZAPIACON23 An entry such as MAHLER The Song of the Earth would yield the key MAHLSONEAR, and BRITTEN A Ceremony of Carols would give BRITCERCAR. Additions and alterations to the Ignore list and Split characters list (see below) can be made at any time by choosing Index=>Show details Ctrl K ) from the main menu. Click Modify after making changes. 4.5.4 Further refinements We can do even better. It is more natural to write No.23 (no space) than No. 23 but, so far, we have regarded only the space character as a word-separator. If we can make the field split at characters such as full-stop, hyphen, comma, semicolon and colon the key structure will be much more versatile. We can achieve this by entering the required Split characters as a string, i.e. .-,;: Where numerals form part of the key, but the key isn t wholly numeric* there can be a problem with the ordering in the index and hence the order of records in a report. The problem would be particularly bad if we were cataloguing all Haydn s 104 symphonies. Using the key structure devised earlier this would involve keys such as: HAYDSYM102, HAYDSYM17, HAYDSYM45, HAYDSYM6 Surely that s not the order they should be in? It isn t, but that s the order we d get! The reason is that the computer doesn t regard numerals in a character string as anything special. They are merely characters, no different from letters and punctuation. Each character is represented internally by a number called the ASCII code and the ordering of characters is determined by these codes. Any string of numerals beginning with therefore comes before one beginning with , even though the strings might be 1000 and 20. There is a way round this problem and that is to pad all our numbers to the same length by adding spaces to the of the string. Powerbase will do that for you in keys if you select Justify numbers Pad with spaces ! That adds spaces to the .) The four keys shown above will then come out as follows: HAYDSYM 6 HAYDSYM 17 HAYDSYM 45 HAYDSYM102 and will appear in the correct order because the ASCII code for a space is less than that of any numeral. Corpus font is used so that the justification of the numbers is clearly visible.) 4.5.5 Other matters concerning keys You will have observed that a word shorter than the number of characters specified is used just as it is, resulting in a short key. There might be times when you want short words padding out with spaces to give a key of full length and selecting Pad with spaces in the Key Structure window will do so by adding the requisite number of spaces to each section of the key. Don t confuse this with Justify numbers (see 4.5.4 Another factor to be decided is whether the index ing is to pay attention to the case of letters, i.e. if they are capitals or small letters By default index ing is case-sensitive. Thus if a record has the word Horse as the contents of a key field and the first four letters are used as the key then the entry in the index will be and you may search for it by entering etc. This may not be what you want. If you require index ing to be case sensitive select the button labelled . Forcing to upper case as described above does not then take place: are inserted and strings are searched for as is . The field containing Horse will be index ed as and only that precise combination of upper and lower case letters will successfully find it. Alphanumeric will be ordered according to the ASCII values of the letters. Since lower-case letters come later in the ASCII table than upper-case ones a record containing would appear after one containing Horse , whereas one containing would come before Horse Omit null keys is shaded at this stage since null primary keys are not allowed. Only if you create a subsidiary key do you have to decide whether to include nulls in the index or not. Building the empty database Once you are satisfied click Create and blank Database and PrimaryKey files will be created. The record window is redrawn and you may start entering data at once. Renaming a database Use the Rename database option on the Miscellaneous submenu to do this. If you rename via the filer your database will lose its icon and revert to the default application icon. Rename database is duplicated on the iconbar menu so that you can rename at the record-design stage. 0hK - 0(( - 0x_ - 0 7 - - - 7 08g - 00L - These three may be reduced to a single mouse-click if you use the Default database option NAME Fred Bloggs ADDRESS 27, Every Street Anytown Woolshire WL4 7XZ DrawPlus Homerton.Medium small_dir small_dir small_dir harddisc ////////////////////// ////////////////////// ADFS::HardDisc4 small_dir Pictures small_dir People Places small_dir small_dir Animals small_c85 .-,,,-./ /.-,,, /---, -..,, --,,, small_c85 .-,,,-./ /.-,,, /---, -..,, --,,, small_c85 .-,,,-./ /.-,,, /---, -..,, --,,, Horse small_7f5 ///// ///// //////// small_7f5 ///// ///// //////// small_7f5 ///// ///// //////// !MyPhotos !Elements !Friends small_dir Databases FredsData OtherStuff Standard It is not usually sensible to build up derived from Numeric field s out of separate bits in this way and, indeed, Powerbase t allow you to do so. Numeric should use one key-field only, the key being the actual numeric value of the field contents. ASCII stands for American Standard Code for Information Interchange. Each uppercase letter, lowercase letter, numeral, punctuation mark etc. corresponds to a number in the range 32 255. (Numbers below 32 are non-printing control characters.) e.g. the numerals 0 9 have consecutive ASCII values from 48 57, the uppercase letters have consecutive values 65 90 and the lowercase letters from 97 122. The numbers are used to store the characters within the computer and on disc. Ordering strings according to their ASCII codes means that those beginning with a numeral will come before those beginning with an uppercase letter and the latter will come before those beginning with a lowercase letter. Ch 5 Input Validation and Validation Tables Powerbase can apply two kinds of data input validation Character validation Table validation Character validation This means exercising control over what characters a field will accept. RISC OS provides the means to allow writable icons to accept only certain characters, or ranges of characters, and reject others. The disallowed characters are simply ignored so that attempts to type them have no effect. If you Shift/double-click on the Powerbase icon the contents of the application directory will be displayed. In it you will see an ordinary directory called Resources and inside Resources you will find a file called ValStrings . You are recommended not to alter this file unless you understand exactly what you are doing and to keep a copy of the original. Examining it can, however, give you an insight into the validation process. Look, for example, at the entry beginning 03 Numeric:E: . This refers to field type 3 (Numeric) and tells us that this field type is Editable. The rest of the line is the validation string of the writable icon which makes up a numeric field . It looks like this: A0-9.+\- ;Pptr_write,4,4 The initial is a command which signifies Allow and is the part of the validation string which especially concerns us. It determines what characters the field will allow you to type. In this case they are the numerals 0-9, the decimal point, plus and minus signs and <space>. Why the back-slash before the minus sign? Because is a special character in an icon validation string (used, as in the 0-9 part of this example, to specify a range of characters) and so are the semicolon, tilde (~) and the backslash itself. To include any of these four characters in the validation string you must precede it with a backslash. The next character is a semicolon which indicates that a new command follows. which comes next is the command pointer ptr_write is the name of the sprite used to represent the mouse pointer when it is over this type (and many other types) of field. You will have noticed that the usual RISC OS arrow changes to a blue vertical bar, somewhat resembling the caret, when over a writable field. specifies the active point of the pointer. A couple of further examples should be sufficient. 01 Alphanumeric:E: uses the same pointer type but the characters accepted are <space> (immediately after the for allow ), the upper-case letters A-Z, the lower-case letters a-z, the numerals 0-9, some common punctuation and the character. You might want to add to this list. 06 Calculated:C: uses a different pointer sprite (resembling a pocket calculator) and has no Allow command at all. (You can t type into this type of field; its value is calculated from other fields.) The indicates that this is a Computed field. Validation tables Table validation is used where a record field is only allowed to have certain values . A manufacturing company, for instance, will have a product code for every item it makes and a database of customers which the firm supplies will make use of such codes to identify the items. It is essential that whoever enters data is prevented from typing a spurious code. This can be achieved by linking the appropriate field to a validation table . Fields linked in this way are recognizable by having their text dark green instead of black. Powerbase will allow you to type invalid data into the field initially but, when you click the mouse over a new field or type Return, the linked validation table is scanned to see whether what you entered in the field is on the allowed list . If it isn Powerbase will restore the previous contents of the field and print an error message. It is possible to turn off validation by means of an option button on the Preferences window. It may also be toggled on and off with Ctrl F9 . If validation is turned off the Table button is shaded. Validation table s have other uses too. They can have extra columns containing other data about the products, such as a name and a brief description, and lists can be printed in which this more informative data appears instead of the codes. You can also have Powerbase replace the typed-in code with a longer, more readable, form immediately on entry. Provided the substitute data will fit in the field, replacement occurs when you type Return or click the mouse in another field. If the replacement is too long then the typed-in data will be unchanged. This can be a great timesaver when a lot of data has to be entered. The F.E. college database referred to in 2.2.5 makes extensive use of validation table s for subjects, courses, tutors and schools of origin. Not only does this trap typing errors but the data entry is greatly speeded up when one can type instead of English Language and Literature , yet have the latter printed out on a list by selecting the Expand button in the Print options window. Creating a validation table Choose Create table from the Validation submenu. Give the table a suitable name and enter the number of rows required. This will be the maximum number of items on the allowed list . (It is possible to increase the number later if necessary.) Next place the caret in the first row (row 0) of the scrolling list and enter the width (in characters) and the heading (optional) for the first column of the validation table If the table is to be used to constrain data entry to an allowed list one column may be all you need. The most common situation, however, is to have two columns, the first holding the allowed list and the second being an expansion or explanation of the former. Use the next row of the scrolling list to enter the column width and heading for the second table column. It is possible to use very large and complex validation table s with all kinds of data associated with each item on the allowed list . There is room to specify 20 columns, but even this can be increased if necessary (see You can if you wish choose the foreground and background colours of both the heading and the body of your table by clicking repeatedly with SELECT or ADJUST on the Heading and icons. Whether this changes the foreground or background colour depends on which of the two radio buttons is selected. When you are satisfied with the data click Create and the table will be created and displayed. It is not saved on disc at this stage. You may enter data into it now or later. Entries may be freely altered and overwritten. The table will be saved when you close the database or quit Powerbase but, if you want to play safe, click Update on the tool-pane Linking tables to fields To link your newly-created validation table to a field click MENU over the field and choose Link to table from the Field submenu. This opens the Link window. A pair of bump icons, with the usual pop-up menu alternative, lets you cycle through the tables in memory. When you have the name of the required table displayed choose the column of the table to which the field is to be linked. It is recommended that column 0 normally provide the link (and therefore contain the data items for the allowed list ) and this is set by default. You may, however, link to any column in the table and another pair of bump icons lets you cycle through the column numbers. Select Linked to table and click on . You will see that the foreground colour of the linked field has now changed from black to green. Place the caret in the field and click on Table on the tool-pane . The validation table will be displayed. You may link a validation table to a scrollable list. The link is to a specified column of the list (make sure the pointer is over the required column when you click MENU) and, at present, only column may be so linked: you can t link two different validation tables to two columns of the same scrollable list. 5.4.1 Replace on entry feature If you select this button before linking the table a third pair of bump icons becomes active, allowing you to choose which column of the table will replace the data which you type in. In the F.E. college database referred to in 2.2.5 the short subject codes (e.g. CHE) would be in the first column and the longer names (e.g. Chemistry) in the second. On typing Return> Powerbase would replace it with Chemistry. In such cases both the entries in the allowed list proper and those in the replacement list are considered equally valid and either may be entered. You could actually type Chemistry if you wished, instead of just , but the former would obviously take longer to enter and you are far more likely to make a mistake, in which case Powerbase would object. 5.4.2 When to turn off the Exact match button Exact match button is normally selected to indicate that the only user inputs which will be accepted are those which exactly match an entry on the allowed list . Deselecting the button allows you to type inputs which are longer but not shorter than the entries on the allowed list so long as the leftmost part of the input matches such an entry. This feature is intended for use with the Replace on entry facility. The unmatched of your typed entry will be attached to the end of the replacement string. Suppose your database records numbers of items called Widgets, Doodahs and Thingummies. You decide to identify these names with the codes W, D and T you would normally put the codes in the first column of the table and the names in the second and select Replace on entry . You can then type <Return> and it will be replaced with Widgets. What you might want, however, is to type and have it replaced with Widgets, 7. Deselecting Exact match allows you to do just that since the W in W7 matches an entry in the validation list. The expanded entry in the second column is made to read Widgets, (note the comma and space) and Powerbase tacks the unmatched part of the entry onto the end of this so that is replaced with Widgets, 7. 5.4.3 Conditional validation There are times when the validity of a data item in one field might depend on the contents of another field. In other words, entering a value in the first field which matches an item in the allowed list of the table isn t good enough; there must be a specific value in the second field as well. This sounds a bit complicated and a hypothetical example might make it clearer. For the purpose of providing suitable ancillary courses, A-level students in a Further Education College are categorised according to whether they take all Science/Maths A-levels (code=S), all Arts/Humanities (code=A) or a mixture of both (code=M). These single-letter codes are entered in a field with the tag TYPE and the ancillary courses are entered in a field called ANCL. Some of the ancillary courses are suitable for anybody, others are specific for science or arts students and yet others are especially aimed at the mixed-discipline students. These courses are listed in a validation table which has three columns. Column 0 contains a 3-letter code for rapid entry of the course in ANCL and column 1 contains the full title of the course for printing on class lists, timetables etc. The final column (2) indicates which type of student the course is suitable for. More than one type may be entered by separating the codes with commas. Thus, a course on the history of science might be aimed at science students and mixed discipline students and would have S,M in column 2. A course suitable for everybody would have A,S,M in column 2. In the Link window we link ANCL to column 0 of the table, thus ensuring that only courses which are actually in the table are acceptable. We also select the option button to the left of the writable icon, enter TYPE in the writable icon, and click the associated bump icons until the matching column is 2. What we are saying here is that merely entering any old course from column 0 won t necessarily result in a valid entry; whether it does or not is conditional upon the contents of TYPE. Thus, if we try to enrol a type H student for a course which is only suitable for type S students the entry will be rejected with an explanatory message, even though the course code is valid insofar as it is present in column 0. Displaying validation table Placing the mouse pointer over a linked record field and double-clicking with SELECT makes a small window pop up to the right of the field. This shows all the data which is on the same row of the validation table as the linked item. The item from the allowed list is highlighted in green and the item (if any) to be substituted on entry is shown in red. This feature is very useful if you are examining a database which uses coded data and you encounter an unfamiliar code. You can also make this window appear automatically whenever the caret enters a linked field. To turn the feature on choose Preferences from the iconbar menu and select the Display linked table data button. When linked to a field in the database record the complete table may be displayed by placing the caret in the linked field and clicking Table button ( ) on the tool-pane. Alternatively, any table present in memory, whether linked to a data field or not, may be displayed by selecting it from the Display table submenu (reached from the Validation submenu). validation table menu Clicking MENU over a displayed table offers a menu with entries as follows: Clear removes all data from the table, leaving it blank. Since wiping out a table in this way is pretty drastic you will be asked to confirm the operation before it actually takes place. It is possible to recover the table using all provided the table has not been closed. When you close a database all the validation table s in memory are written to the disc so you will over-write your disc copy with the blank table. Modify brings up the same window as you used to create the table. You may then increase the number of rows, add extra columns, alter the order of existing columns or, indeed, do anything sensible. Be careful about renaming columns; combining this with shuffling the columns about is apt to cause confusion: Powerbase t a mind-reader! When you have made the required changes click on Modify Print outputs the contents of a validation table in the same format as a report. The output appears in a window from which it may be saved as a text file (see 3.1.1 <column> will sort the table on whichever column the mouse pointer was over when you clicked MENU on the table. change will restore the specific item which you were pointing at when you clicked MENU to the state it was in when the table was displayed. You cannot changes if you have closed the table then re-displayed it. all restores the entire table to the state it was in when first displayed, even if Clear has been used. You cannot changes if you have closed the table then re-displayed it. leads to a Save box in which the default pathname points to a directory called ValTables inside the database application directory. Since that s where Powerbase expects to find the tables for a database you should normally accept this pathname by clicking on or typing Return. Only if you are transferring tables from one database to another should you need to drag the icon to a filer window. validation table s in memory are, in any case, saved to the ValTables directory of the database when you close the database or quit Powerbase and you can also make this happen at any time by clicking Update on the tool-pane Save as CSV leads to a Save box from which the table contents can be saved as a CSV file . The default pathname points to the database PrintJobs directory. The file can be loaded into a blank validation table of appropriate format by dropping the file onto the open table (see for further details). Loading validation tables A table linked to a field is automatically loaded when the database is opened. If not yet linked, however, the table won t be in memory unless you have just created it. Show files on the Validation submenu ( Ctrl Q ) will display the ValTables directory and the required tables can dragged onto the record window (or simply double-clicked). Even more conveniently, Show is provided with its own submenu listing the les in ValTables, and tables may be loaded by choosing them from this submenu. Each table is displayed as it loads. There may be times when you wishes to use a validation table to hold some data, but don t want to use it to validate input into a field or to link it to a field at all. It is possible to load a validation table once the database is open by using the procedure described in the previous paragraph, but a more convenient solution is to add a character to the end of the table s name (the usual limit of 10 characters applies to the name length). Powerbase will then load it whether any field is linked to it or not Including validation table data in printouts Validation table s are often used to allow short codes to be entered in records but with a link to a more descriptive entry in another column of the table. If this is all that is required then always put the data which makes up the actual allowed list , i.e. the items which are allowed in the fields of the main record, into the first column of the table (column 0) and set the link from the record field to this column. The more detailed expanded entry should go in column 1. Printing with the Expand button in the Print options window selected will then print the column 1 instead of the column 0 entry. You can also include data from other columns of a table by displaying the table and clicking in the required columns with ADJUST, exactly like selecting main record fields for printing (see ). It is immaterial which row you click on; only the column matters and the highlighting to show which columns are selected always appears in the first row. The columns selected in all tables are saved as part of a print Selection file and may therefore be retrieved for future use. As well as highlighting the required columns you will also need to select the Expand button as described above. Printed reports will then include all the data from the highlighted columns. You will need to use this method if you want to print columns 0 1 of a table instead of printing column 1 instead of column 0 as described in the previous paragraph. Entering validation table data into a record If you place the caret in a database field and then double-click over an entry in an open validation table, the table entry will be copied into the data field, if it will fit. Nothing is copied if the item is too long to fit. This also works for entering validation table data into writable icons in dialogue boxes and into Scrollable lists. In this last case the caret moves to the next cell of the list after the string has been inserted, thus making it very easy to insert several items in quick succession. If, on reaching the last cell in the last row, you hold down Shift while double-clicking in the validation table, a new row is inserted in the Scrollable list. n as de 0(e - 0p] - 0L8 - * If you have closed the database the table won t have been reloaded on opening again. See for how to load your table for linking. * This feature cannot be used on tables created with early versions of Powerbase and the option will be shaded in such cases. The commonest reason for needing to modify such a table is to increase the number of rows. First export the data as a CSV file then create a new table with the required number of rows and drop the CSV file onto it (see Ch 6 Performing Calculations There are four mechanisms for performing calculations Powerbase Computed fields (see 4.2.11 . These are self-calculating and may be time and date-dependent without depending on the values of other fields, or they may derive their values from other fields in the record and auto-update when one of those fields is altered. Calculations which are derived from one or more data fields but are not stored in fields themselves. They exist only for inclusion in reports and results are calculated on the fly during report generation. Calculations which are derived from one or more data fields and entered into another field by means of an Evaluate button (see 4.2.13 Arithmetic performed on a column in a report. Computed fields may be of Calculated or Composite types. Calculated field s display a numeric result and Composite fields display a character string. They are readily recognised by moving the mouse pointer over them: on Calculated fields the pointer shape changes to resemble a pocket calculator; on Composite fields it changes to a large blue . The latter do not, strictly, involve calculations at all but because Composite and Calculated field s work in the same way it is convenient to deal with them together. It isn t possible to type data directly into either of these special types of field. Many of the Stamp fields listed at the beginning of section 4.2.12 have exact counterparts among the Computed fields. The relevant type are (c), (d), (e), (f), (g) and (h), all of which are concerned with time and date. They have identical names to their Stamp equivalents but, whereas Stamp fields are entered when the record is created and don t (apart from Last altered) change thereafter, the Computed types update whenever a record is displayed It is also possible to write your own functions in Basic and use them in Calculated and Composite field s. The system variable TIME$ is useful in this connection. Calculated field 6.1.1 Simple calculations using Numeric field Our first example will be to make a field containing a v.a.t. exclusive price determine the contents of another field which includes v.a.t. at 17%. Let the of these fields be VEX and VINC respectively. Click MENU over VINC to bring up the Field submenu and choose Calculations . A window appears with a writable icon which contains: VINC= Complete the formula so that it reads: VINC=VEX*1.17 and click on or type Return. From now on the value in VINC will change whenever you type something in VEX. Since the v.a.t. rate is liable to change it would be better to place the current percentage rate in another Numeric field called RATE and make the formula attached to VINC read: VINC=VEX+(VEX*RATE/100) The second example calculates the average of four fields whose tags are No1, No2, No3 and No4, placing the result in a Calculated field called AVGE. The required formula is: AVGE= (No1+No2+No3+No4)/4 If you decide to actually try these examples be sure to make the Calculated field either an integer or fixed-point type, otherwise strings of unwanted decimal places are likely to occur! You may also enter a tag into a calculation formula by clicking on the required field with Ctrl held down or by choosing it from the pop-up menu of fields available from the calculation window. The tag will be entered in the formula at the caret position. You are strongly recommended to use one or other of these methods since Powerbase finds it very difficult to decide whether or not a formula is valid and errors are usually detected only when the calculation is actually attempted. 6.1.2 Making calculations retrospective The default setting is such that entering or altering a formula affects only those records added or altered after the formula entry/change. The changes can be made retrospective by selecting the Recalculate existing records button on the formula entry window. On clicking you will be asked to confirm that you want previous records to be made consistent with the formula you have just entered. Changes affect the current subfile only, but can be easily implemented in other subfiles by changing to the required subfile, calling up the formula entry window, selecting the option button and clicking The Preferences window, accessible from the iconbar menu, contains a button labelled Recalculate on opening . If this is selected then calculations involving the system variable TIME$ will be updated automatically for all records when the database is opened (see also 6.1.3 Calculations using numeric field s (!) This isn t as daft as it looks! You might think that the process described in 6.1.1 is only applicable to numeric field s, but a non- numeric field may be specified in the formula it is linked to a validation table . To be of use there must be numeric data in the column of the validation table immediately following the one to which the field is linked (see ). Suppose, for example, we have fields in a student record for A-level exam grades, the field being GR1, GR2, GR3, GR4. The grades are non-numeric but they map onto the numeric points system which universities use to control entry. The relationship of grades to points is as shown at left. A validation table could be set up with the grades in column 0 and the equivalent points in column 1. Each of fields GR1 GR4 would be linked to column 0 thus restricting input to the capital letters A-E. A further field of Calculated type would be created to hold the points score. If we associate this field (PTS) with the formula: PTS=GR1+GR2+GR3+GR4 entering or changing the letter grades in GR1-GR4 will make the correct score appear in PTS. 6.1.4 Calculations involving times There is an Editable field type called Time which will only accept valid 24-hour times in hh:mm:ss format. If the tag of such a field is included in a calculation formula it will be converted into seconds and the result used in the calculation. Thus a Calculated field DIFF could use two Time field s, TIM1 and TIM2, in the formula: DIFF=TIM1-TIM2 Powerbase would keep DIFF updated to show the difference in seconds between the two times. Three times could be averaged and the result (in seconds) placed in a field AVGE using the formula: AVGE=(TIM1+TIM2+TIM3)DIV 3 (The use of DIV here, rather than /, ensures that the result is an integer.) 6.1.5 Calculations involving dates You may also include date fields in a calculation in a similar manner to the use of Time fields (see 6.1.4 ). A Calculated field DIFF could be used to show the number of days between two dates using the formula: DIFF=DAT1-DAT2 Composite field To enter the formula for a Composite field follow the same procedure as for a Calculated field . Note that the relevant entry on the Field submenu now says Combine fields . Formula entry is similar to that for Calculated field s. The result of the calculation is a character string and is usually result of string operations. One of the most frequently used will be which allows fields to be joined together. Suppose your database contains fields for surname (SNAM) and forename (FNAM) and you want to be able to print names in the format forename-surname. Define a Composite field called NAME and attach to it the formula: NAME=FNAM+ +SNAM Note the quoted space separating the names. You might want the NAME field to show only an initial plus the surname. This could be extracted using the Basic function LEFT$ * to produce the formula: NAME=LEFT$(FNAM,1)+ +SNAM Composite field s may be used in conjunction with Time field s to perform genuine clock arithmetic , e.g. referring to the examples in 6.1.4 , if we attached the formulae to Composite fields instead of Calculated fields they would display the difference and average respectively in hh:mm:ss format instead of in seconds. As with Calculated field s updating occurs after editing a field whose tag appears in the attached formula. Thus NAME would be updated after changes to FNAM or SNAM and DIFF after changes to TIM1 or TIM2. You can, however, have Composite field s which make use of the Basic system variable TIME$. Thus a field DAY could be linked to the formula: DAY=LEFT$(TIME$,3) to make it show today as Mon, Tue etc. No field are referred to in the formula so DAY gets updated immediately before displaying the record so that the information is correct at that time. (Note, however that there is a dedicated Composite type for displaying the short-form day of week. See beginning of chapter.) User functions These are functions, written in Basic, which accept field as parameters and can be included in the calculation formulae self-calculating fields. All such functions must return only their principal value: RETURN variables in the parameter list aren t allowed. The name of each function must begin with an upper-case , e.g. FNUaverage. This avoids duplication of function-names which occur in Powerbase Each user function to be used by a database should be in the form of a separate Basic file. The files may be called whatever you like, but it is sensible to use names closely related to the actual function names. All the Basic files are stored in a subdirectory of the database called UserFuncs . An entry on the Miscellaneous submenu (keystroke equivalent Ctrl Y can be used to display the UserFuncs directory and, unless the directory is empty, this menu entry will have its own submenu listing the contents by lename. Choosing an item from this submenu loads the relevant Basic le for editing. When the database is opened each function is loaded using Basic s LIBRARY command. Powerbase can then call the user functions just as readily as its own functions. When a user function appears in a calculation formula the tags of any fields on which the function operates are included as parameters to the function. Editing any such field makes the Computed field update. A menu of user functions is available from the Formula window to make them more easily accessible. This is quite different from the menu of lenames referred to above in that it lists the actual function names as given in the Basic DEF FN statement. To enable you to see the number and type of parameters required by the functions these parameters appear in the menu as Basic variable names. When actually using a function in a formula the variable names should be replaced with field tags. The distribution disc includes a UserFuncs directory containing examples of useful user functions, two of which were kindly submitted by David Lenthall. Users are invited to submit other user functions for possible inclusion in the function library. FNUnow operates on the value of TIME$ from the real-time clock and returns a string containing the current date in form DD-MM-YY. A record design could include a field of Composite type, at least eight characters long and tagged as, for example, DATE. Associating the field with the following formula would cause the field to display the current date at all times: DATE=FNUnow(TIME$) If the function always operates on TIME$ and never on any other string, why do we bother including TIME$ as a parameter to the function? A Calculated or Composite field is normally only updated when you edit a field on which its value depends. The field DATE in the above example doesn t depend on any other fields and would therefore never be updated! The inclusion of TIME$ causes the Composite field to be updated immediately before the record is displayed. The same trick of using TIME$ as a parameter, even if the function makes no use of it at all, can be used for any user function associated with a field which you want updating without having to edit the record, but note that updating will only occur if you call up the record for display. See 14.4.3 for how to make records in the current subfile update on opening the database. The second function, FNUageinyrs accepts two parameters, each of which should a date in DD-MM-YY format, and returns the difference between the dates to the nearest year. The first date should be the earlier of the two. If your record design has a field for Date of Birth (let s call its tag DOB) you can use this function together with FNUnow (which returns today s date in the required format) to make a Composite field (AGE) display a person s age in years by using the formula: AGE=FNUageinyrs(DOB,FNUnow(TIME$)) Considerable care is needed in constructing user functions as it is very easy to make Powerbase generate errors. In particular you must avoid giving a function a name which is the same as a field tag or even one which contains a field tag as a substring. The two functions described could not, for instance, be used in a database which had a field tagged as , although and could be used. To avoid this problem users are recommended to form the habit of giving tags names in upper case and user functions names in lower case (apart from the , of course). Calculations for reports only Computed fields based on other fields have the disadvantage of requiring the user either to foresee at the time the database is designed what calculations will be required or reformat it later in order to include a field which might only be needed for a one-off inclusion in a report. To avoid this, Powerbase lets you specify such calculations without the need for a field to which to attach them. In a sense they resemble the column calculations described in section in that they are performed on the fly during the creation of a report. To define a calculation choose the entry Extra calculations Ctrl Shift N ) from the Print submenu. The window which appears requires you to enter a formula, which has exactly the same format as the formulae attached to Calculated and Composite fields (see and ). Since the calculation will not be attached to a field from which to derive a heading, a width, and whether the result is to be numeric or not, you must supply this data yourself. If you omit anything Powerbase will use the formula itself as a heading, assume a Numeric result and assign a width of 15 characters (the maximum space a number can occupy). If you have clicked the radio button to select a String (i.e. non-numeric) result such as would be placed in a Composite field, the width will default to 255 characters. This might seem an enormous value, but unnecessary white space will be removed from the report before printing if the output is to the printer. If the report is to be displayed in a window the Shrink list button on the Print options window should be selected. Failing this you may remove the surplus space by choosing Shrink list from the menu over the report window. It is possible to produce errors by unsuitable combinations of fields and result types (just as it is for Computed fields). The main thing to remember is that Numeric fields will normally produce a numeric result so assigning a String operation to such fields can be expected to produce an error. There are exceptions even to this rule since the Basic STR$() function may be used to convert a number to a string, thus allowing Numeric fields to give a String result. Dates, Times and User Functions may be used in Extra calculations, just as they can in Computed fields. When you have defined your calculation, click on Include . The displayed calculation number will go from 0 to 1 and the window is set up for entry of another calculation. A total of 10 (numbered 0-9) are allowed and you may return to earlier calculations to edit them by clicking the icons. The position of such calculated results in the report is determined by the point at which you define them. If you click ADJUST to highlight a field, then type Ctrl Shift N to bring up the new window and define a calculation, and finally highlight another field, the calculated result will appear between the highlighted fields. The menu of selected fields (see 3.7.2, S ) shows these calculations in red so you can easily check on the order in which things will appear on the report. You can choose the red entries from this menu to bring up the calculation window for editing. All defined calculations are saved as part of a Print Selection and will be restored when the Selection file is subsequently reloaded. The Clear button on the calculation window has the same effect as Clear selection Ctrl Z ) from the Print submenu. Calculations from an Evaluate button The Evaluate button was briefly mentioned along with the Increment button (see 4.2.13 ). It enters the result of its computation into a data field having the same tag as the button. The formula associated with the button is of exactly the same type as for a Calculated or Composite field and is entered by choosing Formula from the Field submenu obtained by clicking MENU over the button. Unlike a Calculated or Composite field the data field does not auto-update when one of the fields referred to in the formula is edited: you have to click the Evaluate button to update the field. This method of performing calculations does, however, have two important advantages: the Evaluate button can be added to an existing database without reformatting and the field which receives the result of the computation can be of any Editable type. Calculations on a column of a report There is an option on the Print submenu called Numeric field which is normally shaded. It only becomes available when you highlight one or more field s of numeric type for printing. These may be editable Numeric elds, Calculated elds or any elds of the Computed or Stamp class which are essentially numeric. You can then access a window listing all the fields (if any) in the record which are of these two types. Associated with each field are six check-box es which can be selected to include the following in the report: Count (number of values in column) Average Standard deviation Maximum value in column Minimum value in column check-box es are shaded until a field is included in a print selection. They then become and any or all of them may be chosen by clicking with SELECT. The information requested is then added to the report footer when printing takes place. To obviate the need to select a lot of check-boxes individually when many numeric elds are highlighted, option buttons are provided which enable all the enabled check-boxes in a column to be selected or deselected with a single mouse click. Another entry on the Print submenu provides a similar facility for Check-box . Instead if six check-boxes for each qualifying field there is just one which, as before, is shaded unless the field is included in a print selection. For 2-state check-boxes the information printed at the foot of the report consists of the numbers selected and unselected. For 3-state boxes the number in each of the three states is printed. The results are labelled and Neither , except in the case of the F/M/<blank> box where the labelling is Female As in the case of numeric elds, an option button allows all the enabled check-boxes in the window to be selected or deseleted. eport 0X= - 0`{ - * LEFT$(string$,n) extracts the leftmost n characters from string$. RIGHT$(string$,n) extracts the rightmost n characters. MID$(string$,n,m) extracts m characters starting at the n th character from the left. Grade Points 10 8 6 4 Ch 7 Using extra indexes The record data is stored in a file called Database inside the database s application directory. The order of records within the Database is determined by the order in which the records are entered. To access the records in a logical order we need one or more indexes Powerbase does have sorting facilities for sorting reports and validation table s, but data ordering is achieved mainly by the use of indexes The most important index index 0. This is always called PrimaryKey and is created as part of the process of setting up a database (see ). You will find the file PrimaryKey inside the database directory. Other, subsidiary indexes are stored in the database Indexes directory and all have a large on their icon. When a database is opened any indexes in this directory will be loaded automatically. ( Indexes operate in memory to achieve maximum speed not from disc.) Changes to the database update the indexes and closing the database (or quitting Powerbase itself) causes the updated indexes to be written back to the disc. Note that the more indexes you use the longer it takes to add and delete records or to make alterations to records which involve changing indexes . The ideal situation might seem to be to have fields index ed and, given a fast enough machine and a record structure with not very many fields, this might be feasible. Remember that fields which are repetitive (those which have only a few distinct values repeated throughout many records) do not index efficiently. Subsidiary indexes can speed up report printing in some cases (see ). When you enter a query Powerbase will see if a subsidiary index can be used with advantage and, if so, will go ahead and use it. To indicate that this is happening the number of the subsidiary index will appear in the icon at the top right of the query panel (also used to indicate when records are marked). For the speed-up to work the case button on the query panel must match the case-dependence of the index. This is a point easily overlooked! Indexing a field Click MENU over the required field and choose Create Index (Ctrl J ) from the Field submenu. The Key structure window will appear with the tag of the field you clicked on in the first of the four Field icons. You may use the bump icons or pop-up menus to bring the of the other fields into view if you wish to base the index on more than one field. Enter the data in exactly the same way as when creating the primary key index (see ) and click on Create . If the field is already index ed you will be warned of this and asked if you wish to overwrite the existing index . When index ing is complete you will see that the descriptor of the index ed field has changed from black to dark blue. If you click the button on the tool-pane Shift F4/F5 ) the red highlighting of the descriptor which indicates the current key moves from one index ed field to another. When you have a subsidiary key selected as the current key it works just like the primary key as regards browsing, searching and printing. There is nothing to stop you index ing one field whilst a subsidiary index based on another field is the currently-active one, thus producing what amounts to a sort within a sort. One button which was shaded when creating the primary key is the option button Omit null keys . Although null primary keys aren t allowed null subsidiary keys are and the default setting for this button is therefore OFF. The field being indexed might in some cases be null most of the time and you want to index only the records in which it isn t null. If so, select the button before clicking Create. Index entry on the main menu leads to a submenu offering four choices. Show details Ctrl K ) displays the structure of the currently active key. Show files Ctrl I ) opens the Indexes directory and, unless the directory is empty, has its own submenu listing the names of the les. Choosing from this menu makes the chosen index the currently-active one. Delete allows you to remove an index (after confirmation). The index file is moved from Indexes into a subdirectory called Deleted . Such an index is no longer active and will not be loaded when the database is opened. You can, however, restore it by moving it back into Indexes but it is only useful to do so if no records have been added to or removed from the database in the meantime. Rebuild displays the structure of the current index and allows you to rebuild it, with or without modi cation. Automatic saving of indexes Indexes are written to disc when you close a database or quit Powerbase but, as long as the database remains open, they are in memory and therefore could be lost if the power fails or the computer is switched off. Occasional use of the tool-pane Update button ensures that the disc copy of all indexes is more or less up-to-date but you can automate the process if you wish. Choose Preferences from the iconbar menu, make the appropriate selection from the Save indexes section of the Preferences window and click on Accept . You may make Powerbase save the indexes at regular intervals or simply warn you to do so. 0xL - Ch 8 Exporting and Importing data - CSV files What are CSV files CSV stands for comma-separated values and is the name given to files consisting of lines of data, each of which contains individual data items separated from each other by commas. Such files are widely used to mail-merge using a wordprocessor (see ) and also to transfer data from one application to another, e.g. from a database to a spreadsheet or from a RISC OS database to a PC database. We will refer to each line in a CSV file as a record and each item of data in such a line as a field since records and fields are the source and destination of such data when it is exported from or imported into a Powerbase database. The following points should be noted: Fields which are non-numeric (e.g. plain text items such as names and addresses) are often enclosed in double quotes ( ) whereas numeric data is not. This makes it possible for an application reading a CSV file to distinguish between numbers and strings (which might of course contain numerals) and also allows a comma to be used as a character within a string without being mistaken for a data separator. (Addresses often contain commas e.g. 112, Keighley Road). For many purposes the quotes aren t necessary and may be omitted. Null fields are usually included and can be located by looking for two commas with either nothing in between them or with only two double quotation marks between them, i.e. ,, or , ,. Each record in such a file will always contain the same number of fields. Powerbase does, however, allow nulls to be omitted entirely when creating a file in which case the number of fields per record will vary. The way in which each record is terminated varies from one system to another. The last field in a record is followed not by a comma but by a line terminator . On RISC OS systems this is the same as in ordinary text files; the linefeed character (LF, ASCII value 10) and on PCs it is usually the carriage-return character (CR, ASCII value 13). You might, however, encounter CSV files in which both these characters are used, i.e. LF CR or CR LF. You can define any character or character-pair as the record terminator Separators other than commas are sometimes used. The Tab character (ASCII value 9) is often used and such files are called tab-separated values files Powerbase lets you define any character, or even a pair of characters, as the field separator. All such files created by Powerbase will be of type &dfe and display the CSV file icon (although the default filenames offered do differ: CSVfile where the separator really is a comma, TSVfile where it s a TAB and ?SVfile otherwise). In what follows we will, for convenience, refer to them all as CSV files whether or not the separator is a comma. Some applications which accept a CSV file as input expect the first line to contain the names of the fields which comprise the subsequent records, e.g. if each record consists of a name and a four-part address this header record might read: STREET COUNTY POSTCODE Powerbase can both export and import files with such a header. Setting the CSV options Choosing Export CSV file => Options from the main menu displays the CSV options window which lets you specify all the file characteristics described earlier. Pop-up menus give you a choice of field-separators and record-terminators with space to enter your own if you wish. The first three option buttons cause an exported file to have, respectively, the following characteristics when the button is selected: quotes round non-numeric fields see (1) above a header record specifying the field names as either tags or descriptors see (5) above null fields included see (2) above Note that the field names referred to in a header record are, by default, the of the corresponding Powerbase fields, but may be changed to the descriptors by altering the setting in the Print options window. Exporting data as a CSV file Having set up your options as described above, creating a CSV file is very like printing a list. First highlight the fields to be exported by clicking on each with ADJUST. Remember that the order in which the fields are highlighted is important. If a default print selection exists (i.e. a file in PrintRes called !Selection ) it will be used to determine which fields are exported provided no alternative selection has been made. Next choose Export CSV file => Export (Ctrl X) . A window featuring the Query panel appears. Type in a search formula to determine which records are exported. Finally, enter the name of the file and drag the file icon to a filer window. By default the file is saved in PrintJobs as usual, and you may simply click on the Export button or type Return. Experiment with saving CSV files with different settings of the CSV options and then loading the resulting files into Edit to examine them. If you select the Reverse button on the Query panel the CSV file will be created in reverse order. (See also field-concatenation option ( see 3.2.2 ) applies. This means that data which occupies separate fields in the Powerbase record need not do so in the exported CSV file. By holding down Shift when selecting the field with ADJUST, the comma (or other separator) which would normally follow is suppressed until you select a field without using Shift. A slightly problematic situation occurs when you concatenate a mixture of numeric and non-numeric fields with the In quotes option selected. In such a case Powerbase will enclose the whole concatenated group within two sets of double quotes. e.g. NAME, Z, M and SYM from the Elements database would be exported, for actinium, as: ACTINIUM 89 227 Ac Spacer see 3.10.2 in the above instance it is the default setting of one space is used to separate the concatenated fields. To export the displayed record only hold down Shift when starting the CSV export. 8.3.1 Scrollable lists and CSV files When you include a Scrollable list as one of the fields in a CSV file it will be written as a single CSV field if the format is selected in the Print options window is As single line . The character used to separate data from different rows of the Scrollable list is set in Scroll term in the CSV options window and the default is a semicolon. The data from individual cells in the same row of the scrollable list are separated by the same CSV separator as is used between CSV fields. The above description might seem confusing but will become clearer if you export data from the sample database Scroller using the default settings but with In quotes selected in CSV options. You will see that the entire Scrollable list from each record is enclosed in double quotes, between which the items from within each row are separated by commas, a semicolon marking the end of each row. A file exported in this way can be re-imported into a Powerbase database. You must ensure that the scrollable list data goes back into a scrollable list field, although it need not have the same number of columns as the one from which it was exported. If the new list has more columns than the old there will be one or more blank columns on the right hand side; if there are fewer columns data from the missing columns will be discarded. This closely resembles what happens when there are more or fewer fields in the database than in the one from which the file was exported (see 8.4.2 If you change the CSV printing format in Print options from As single line As columns and export the CSV file again (in quotes as before) you will see the contents of each row enclosed in double quotes and separated by commas, the items from within each row being also separated by commas. Exporting this second file without the quotes (or the first file if you first change Scroll term to a comma) will result in a file in which every single cell of the scrollable list appears as a separate CSV field, separated from the next by a comma. This might prove useful if transferring data from a database which contains a scrollable list to one which doesn Transferring data from and to scrollable lists in individual records was covered in 2.6.4 Using CSV files import data If you drop a CSV file on the record window of an open database the CSV options window (see ) appears with the title changed to Import CSV file and some additional icons, one of which displays the pathname of the file. Clicking on Import will make Powerbase try to create new database records from the file. If you decide not to do this you should click on Cancel . If you do wish to import the data there are some important consideration which will now be explained. 8.4.1 Ensuring that the correct options are selected If the CSV file originally came from a Powerbase application the settings in the CSV options window need to be exactly the same as they were when the file was exported. The exception to this is the In quotes button which is shaded on import because Powerbase doesn t need it. If the CSV file came from a PC or another RISC OS application you might have to load it into Edit to find out what separator terminator are used. You will also probably need to set the filetype to &dfe (omit the ) as well. There are three more option buttons at the bottom of the window. One causes each record to be displayed as it is imported. The import process is slower with this turned on but much more informative if you like to know how things are progressing. The second button, when selected, strips any trailing spaces in the imported data-fields. Some database programs pad all fields to their maximum length by adding spaces to the end of the data where necessary. If you import such a file into Powerbase you will find that the caret will always be at the far right of the field even though visible characters do not fill the field, and some queries won t work properly. Setting the Strip spaces button before importing the file overcomes the problem. The third option button determines how Sequence number fields are handled. If the button is ON imported sequence numbers are ignored and new ones assigned in accordance with the field s sequence number counter. With the button OFF sequence numbers from the CSV file are imported without alteration. 8.4.2 Directing imported data to the correct fields If no fields on the record window are highlighted (i.e. with ADJUST) and the CSV file does not contain a header record (see ) then the import process proceeds according to the following rules: The first CSV field will be read into the first Powerbase field for which importing is allowed, This means any which can hold text (including External fields) or numbers and also check-boxes. Graphics fields, Buttons or fields which are merely labels will be ignored. The next CSV field will be read into the second Powerbase field and so on. If the end of the CSV record is reached before all the relevant fields have been filled ( data underflow ) then the next CSV record will start a new Powerbase record, i.e. the reading won t get out of step. It does not matter, therefore, if the CSV file omits null items at the end of a line If all relevant fields are filled before reaching the end of the CSV record ( data overflow Powerbase ignores the remainder of the line and skips to the beginning of the next CSV record before starting a new Powerbase record. This is also to keep the operation in step. There will be occasions when you don t want to fill the Powerbase fields sequentially as just described. There are two ways of making the process more specific: Highlight the required Powerbase fields with ADJUST before starting the import. Data will then be read only into the highlighted fields, all other fields being ignored. The order in which the fields are filled is the order in which you highlighted them. The rules given above about underflow and overflow of data still apply. Give the file a header record containing the descriptors of required fields in the Powerbase record. There is nothing to prevent you from using Edit to add such a header to a CSV file which did not originate from a Powerbase application. The format of the header is illustrated in (5). Importing then occurs just as if those fields were highlighted. The Print options window must reflect whether the descriptors of fields are used. Do not use both a header highlighting. 8.4.3 Importing data from plain text files It was explained in (4) that files created as described above ( can have separators other than a comma. If the file has been created using Powerbase CSV exporting facility it will have been given the file-type &dfe as if it was a true CSV file. If it comes from some other source it is likely to be of type &fff (Text). It can still be imported but caution is needed because there are other circumstances in which a text file might be dropped on the record window . A properly-written script file (see Ch 12 ) would be recognised as such and therefore cause no problem, but any text file dropped onto the appropriate type of External field (i.e. a Text, Text Block or Remote field) would become linked to that field instead of being treated like a CSV file. If you are importing data from a plain text file and your record contains fields of the aforementioned types be sure to drop the file on the window background, not on the External field The appropriate window will then appear with the title Import text file You are strongly advised to use proper CSV files if at all possible. You could, in fact, use the filer to set the filetype of such non-standard files to &dfe (omit the ) so that the problem of Powerbase taking the wrong action doesn t arise. Be sure to use Options to set the correct field separator and record terminator though. 8.4.4 What if the imported data won t fit? There are two situations in which this can happen. The database might not contain enough free records to hold all the imported data and so you get a Database full when reading CSV file error. To avoid this either make sure the database is big enough before you start or place a suitable value in the Increment for expansion icon in the Change length window . The latter is accessible from the Utilities submenu of the iconbar menu (see The second situation is where an item is too long for the destined database field. When importing data Powerbase maintains a file called TooBig inside the database PrintJobs directory. Anything which won t fit in the target field is instead written to this file together with information about where it was intended to go and an character is placed in the database field to draw your attention to it. No writable Powerbase field may be longer than 246 characters and if an item of imported data exceeds this a note will be made in the TooBig file advising you to define an External field Text Block or Text) for such data. After completing a CSV import operation it is advisable to look at the TooBig file to see if any remedial action is needed. Using CSV files to modify existing records Most database programs which support CSV import allow it to be used only for creating new records. Powerbase is unusual in that you can use a CSV file to modify existing records This capability should be used with caution since careless use can irrevocably garble a database. There are three relevant radio buttons in the CSV options window. They are called Modify existing, With primary key and With record number and their actions are as follows: Modify existing No new records will be created when a CSV file is dropped onto the record window. The existing records will be accessed in the order determined by the current index and the new data will be merged into these records. You should, of course, either use ADJUST to highlight the fields into which the data is to go or place a header in the CSV file specifying the field tags and then turn on the With header button (see 8.4.2 ). If all the records are modified before the end of the CSV file has been reached a warning message is displayed. Note that it is the s responsibility to ensure that the data in the CSV file is in the correct order since Powerbase has no way of telling which data is destined for which record and can only proceed sequentially. With primary key This affects both export and import. When you export data with this button selected each record of the CSV file includes the primary key of the Powerbase record. (Try creating a CSV file with and without this option set and compare the files using .) When importing such a file Powerbase will attempt to locate records with the same primary keys as the records in the CSV file. If a matching key is not found a new record will be created to receive the data. If the key does exist then the CSV data goes into the same record, overwriting any data which the target fields already contain. The option is only useful for transferring data between Powerbase databases which have the same primary key field(s) and structure. t forget that Powerbase , by default, allows duplicate primary keys so it is possible for the new data to go into the wrong record. You shouldn t trust this method of import unless you are sure each primary key is unique. With record number This resembles the previous option. It allows data to be exported with the Powerbase record numbers included. Importing into another (or the same) database places the data in records having the same record numbers, again overwriting data which may already be present in the target fields. This option is only useful for transferring data between Powerbase databases in which corresponding records have identical record numbers. One use for it might be to export a set of data, load it into an editor and do some extensive searching-and-replacing, then put the modified data back into the original database. When any of the above three buttons is selected, clicking Import will warn you what is about to take place and ask for con rmation. If you cancel the operation the radio button will be deselected and the normal default ( Create new records) selected instead. Importing data into validation tables It was explained in section that the contents of a validation table can be exported as a CSV file. You may also import data by dropping a CSV file onto the table. The Options window appears as described in so that the appropriate separator and terminator can be selected if necessary. It is not possible to increase the number of rows or columns in a validation table by this method. Only those items for which a space exists will be imported; the rest are ignored. Over-long data items are truncated. If a CSV file is dropped onto a table with columns than there are fields in the CSV record, one or more columns in the table will be left blank. This facility makes it possible to convert an old-style (i.e. non-modifiable) validation table to a new-style (modifiable) one without having to re-type all the data. First export the data as a CSV file, then create a new table (which may have more rows and/or columns than the old one if you wish) and drop the CSV file onto it. If you give the new table the same name as the old one the former will overwrite the latter when the database is closed. Creating a new, working database from a CSV file There is an option button in the CSV options window called With field data which only becomes active when the With header button is selected. With the button selected the header record of a saved CSV file contains not only the field names but also the field lengths and types. Again it is suggested that you create a file and look at it in Each item in the header contains four items of information separated by | (vertical bar) characters. These are, in order: the field type number (These may be examined in the file !Powerbase.Resources.ValStrings. the maximum length of the field in characters the field descriptor the field tag The CSV separator, as used in the remainder of the file, is used to separate each of these groups from the next. Assuming the separator to be a comma the structure of the header is: <type>|<length>|<desc>|<tag>,<type>|<length>|<desc>|<tag>,<type>|<length>|<desc>|<tag> ... It is quite possible to take a CSV file from a non- Powerbase source and add such a header to it by hand. The field-type can be omitted, in which case the type is set to 0 (Unrestricted) and remaining number is assumed to be the length. It is also possible to omit either the descriptor or tag, but not both. If only one string is supplied it will be used for both descriptor and tag. The minimum specification for each field is therefore: <number>|<string> You can force a null descriptor by putting two bar characters together (a null tag is, of course, not allowed): <number>||<string> A file created in this form is meant to be dropped onto the window of an open database. It should be dropped onto the Powerbase icon on the iconbar when no database is open. There is enough information in the header to enable Powerbase to convert the file into a functioning database . All the fields will be ranged on the left of the record window , one beneath another and the primary key will consist of the first four characters of the first field, but these characteristics can be changed if desired. Don t try to create databases containing Check-box , Button or External field s using this method; it s only meant for the most basic type of database in which all fields are of the Editable class. Other methods of exporting data It was noted in section 2.5.2 that the contents of a record field can be dragged by means of SELECT and dropped into another field, a writable icon in a dialogue box, an editor or wordprocessor, or a filer window. In the last case the data is saved as a text file named from the of the data field. This drag-and-drop method can be extended considerably. If a number of fields are selected, as in a print selection, then dragging and dropping as above will transfer the whole selection to an editor, wordprocessor or to the filer (where it is saved as a file called Selected ). You can t transfer multiple fields between databases in this way though, and dragging such a selection to a writable icon transfers the first field in the selection only. When dragging a selection like this the pointer can either be over a field (not necessarily a selected one) or over the window background; it doesn t matter. An even more powerful extension is the ability to export the exportable fields in a record by holding down Ctrl and dragging from anywhere in the record window. If you have two identical databases open this method copy a record from one to the other as well as to editors, wordprocessors and the filer. The main menu has an entry Export selected which displays an ordinary Save box. This is an old feature of Powerbase , now superseded by the drag-and-drop facilities described above apart from the fact that the older method lets you choose your own filename for saved data. 08S - 0hV - Ch 9 Mail-Merging with Impression, Ovation Ovation Pro Although the title refers to Mail-Merg the process to be described is more accurately termed Data-Merg since it is by no means limited to merging names and addresses into letters or labels; any kind of textual data from a Powerbase application can be merged into any kind of document in Impression Ovation (including Ovation Pro or many other editors and wordprocessors. Mail-merg ing with Ovation Ovation expects data for merging to be supplied as a CSV file. An example file ( Ovation ) is supplied with this version of Powerbase. It was created from the Elements database and is intended to work with the sample Ovation document OmergeTest . Before attempting to create CSV files of your own you are recommended to read the Powerbase documentation on such files (see ). The process for mail-merg ing with Ovation is described below. 9.1.1 Preparing an Ovation document for mail-merg Ovation has a very easy method of specifying where the merged data is to go into the document. Whenever you reach a point where you wish to insert a Powerbase field type Ctrl I . A window headed Merge tag will appear inviting you to enter a number. The number required is the field number in the CSV record (which is necessarily the field number in the Powerbase record). If, for instance, you wish to merge the second field from each line of your CSV file enter 2 and type Return. You will see that [Merge2] has been inserted into the text at the caret. Continue typing, inserting other fields as required. The fields do not need to be in numerical order (you can insert field 2 before you insert field 1) and the same field may be inserted many times. Styles and effects may be applied to the Merge tags. Load OmergeTest to see how this looks. 9.1.2 Merging the data from Powerbase With OmergeTest loaded into Ovation , open the main menu, go to the File submenu and choose Print . Select the Mail merge option button and drag Ovation to the writable icon to the right of the button. The pathname of the file will appear in the icon. Click on and the document will be printed once for each record in the CSV file, merging new data into the document each time. Ovation contains 10 records so if you just want to see the process in action without actually printing 10 sheets of paper set your printer manager to print to a file instead of to the printer itself. If you examine Ovation you will see that each field is enclosed in quotes. This is only necessary of the fields contain literal commas (see ). Look at the first record, for example, and note the comma in (Gr.aktis,aktinos; ray or beam) . Without the quotes Ovation would interpret the comma as a field separator and the merging would get out of step for that record. Mail-merg ing with Impression Ovation Pro The following description applies to Impression Publisher and Impression Style . The older Impression and even Impression Junior is also supported. The name Impression may be taken to refer to any of these. Direct communication and data-transfer between Powerbase Impression Ovation Pro can take place without the need for a CSV file. This task is performed by a module called ImpulseII which Computer Concepts have placed in the public domain to encourage its adoption as a standard means of transferring data between RISC OS applications. The !Powerbase.Resources.Modules directory contains a copy of the Impulse module. To use this method of data-transfer with Impression no preliminaries, other than the preparation of the document itself, are necessary but with OvationPro you must first do the following: Open the Applets subdirectory inside OvationPro and copy the !Impulse applet into it. If your OvationPro is a recent version, supplied on CD, you will find this applet in the DavidPilling.Applets directory. The filer also needs to have the !TransDDF filter which is in the directory DavidPilling.Filters on the CD. The !Boot and files of this filter need to be modified to load ASCII DDF files as described in the !Help file. 9.2.1 Preparing the document for merging Type the document in the normal way but leave about three spaces, instead of one, between words where data from a database is to be inserted. We need to insert at these points embedded merge tags which the wordprocessor will use to tell Powerbase what to transmit. Impression Ovation Pro employ their own methods, described in their documentation, for entering merge tags. Powerbase does, however, have its own provision for inserting merge tags and you will find this by far the easiest way of preparing the document. Section explained how fields can be dragged and dropped into a wordprocessor. If you hold down Shift and then drag and drop the effect is rather different. Open a blank Impression Ovation Pro document and drag to it (without Shift) a field from a database, e.g. the NAME field from Elements . The contents of the field, i.e. the name of whatever element is displayed, will appear in the document. If you repeat the operation with Shift held down what you will see is: Impression the field tag in angle brackets, e.g. <NAME> * Ovation Pro , the string Merge 1 in square brackets, i.e. [Merge 1] These are the ways the applications use to show the presence of Impulse commands. The scheme used by Impression is more informative since it tells us what field will be merged. In Ovation Pro the number after Merge is increased by 1 every time a command is inserted. The procedure described inserts the Impulse command GetField into the document. During a merge operation the GetField command will be sent to Powerbase which will pass back the contents of the field specified for insertion into the document. There are two other useful Impulse commands supported by Powerbase and these require an alternative method for their insertion into the document. From the main menu choose Insert merge tags . The window which is displayed has a pop-up menu and bump icons to choose a field and a second pop-up menu to choose a command. First on this menu is GetField , although you ll probably find the method described above more convenient for inserting this command. Second is GetExpanded which can be used with fields linked to validation tables (see ) to insert data from any column of the table. A submenu enables you to choose the column. The default is 1 which is the second column of the table. Merging usually produces one copy of the document per record. This isn t always what we require: sometimes we want to merge data from several records into the same copy of the document. NextMatch allows us to do this. Whenever a NextMatch command is encountered, Powerbase starts transmitting the data from the next matching record without issuing an instruction to print the document or start merging afresh. This opens up the possibility of producing very fancy Powerbase reports by merging many records into a suitably-formatted document. When inserting a NextMatch command it doesn t matter what field you have displayed in the Insert merge commands window. To actually insert a command into the document, first ensure that the correct field and command have been chosen, then place the caret at the required place in the document and drag the text file icon into the document from the Insert merge command window. 9.2.2 Merging the data Make sure the wordprocessor has been by the filer then open the Powerbase database. Data merge on the main menu opens the Data merge window and the prepared document may then be dropped on either this window or on the record window. Choosing Data merge from the menu isn t really necessary, however, since dropping the document on the record window will open the Data merge window anyway. If the wordprocessor isn t already running Powerbase will run it for you. Clicking on Merge extracts data from either the first record or, if the Merge from displayed record button is set, the currently-displayed record, and inserts it in the specified places in the document. You can see this happening if the relevant parts of the document are visible. Using the tool-pane to browse, search or change subfiles or keys now causes the accessed records to be merged into the document instead of being displayed in the record window. Each time a record is merged its data overwrites that from the previous record. When merging is in effect the default action button on the window says Merging instead of Merge If your printer is on-line and the printer driver loaded you may print the document with the merged data by clicking on Print . If the Print all button is selected before Merge is clicked Powerbase will proceed through the database either from the beginning or, if Merge from displayed record is selected, from part-way through the subfile, merging each record in turn and printing for each record a copy of the document containing the merged data. (See 9.2.1 regarding the NextMatch command to modify this behaviour.) More often than not you will want to print only those records with certain characteristics, but where do you enter a search formula to do that? The answer is to use a filter (see 2.3.3 ) to limit the merge to the records you re interested in. With Print all selected, only the records matching the search formula will be printed. It is also possible to control merging via a script file (see Ch 12 ). Only the two commands !FILTER and !MAILMERGE (in that order) will normally be required. s very easy to intiate a merge operation and remember you should have selected Print all ! If this happens to you, click Stop merging . The merged data is removed from the document and the default action button reverts to Merge . You can then select Print all and start the merge again. Cancel has a similar effect to Stop merging but also closes the Data merge window. However, there is nothing to stop you reopening it from the main menu and resuming the merge process, provided the document is still open. During a merge operation (i.e. as long as the default action button says Merging you are prevented from editing the document. Moving the pointer over an Impression document under these conditions makes it change to a large red and blue cross and the caret can t be placed in the document. Over an Ovation Pro document there is no immediate indication but, as soon as you try to type anything, the message t edit. Merge active on this document is displayed. Both Cancel Stop merging remove the merged data from the document and normal editing is restored. If you want to edit the document with merged data present then click on . Note, however, that if you save the document in this state it will be saved with the specific data you have merged into it rather than with the commands to merge data. Appendix A gives a full description of the Impulse commands or methods which Powerbase understands. Mail-merg ing with other programs If you use neither Impression Ovation you may still be able to use mail-merg ing with your wordprocessor, but you will need to read the manual carefully to find out how. It will almost certainly involve a CSV, or similar, file in conjunction with some method of tagging the document (as described above for Impression Ovation ) to specify where in it the data is to be merged. * This feature does not seem to be supported by Ovation prior to version 1.36 and it is to that version that this information applies. * In Impression Impression Junior all merge tags appear in the document simply as <Merge> , i.e. there is nothing to distinguish one from another. To see what the actual Impulse command looks like open Impression Merge command window and place the caret immediately before the initial . The text of the command will then appear in the writable icon. Ch 10 Utilities All the procedures described in this chapter will be found on the Utilities submenu of the iconbar menu, enabling you to examine and alter the structure of an existing database. Note that, if password s have been set, you need to enter the database with the Manager level password to obtain access to this submenu. Changing the Primary Key primary key of a database is determined when it is created but is not fixed for all time. The primary key choice displays the same dialogue box as was used for creating the primary key in the first place, but with the addition of two radio buttons, whose function is described below. The present key structure is shown. Simply alter it to what you require and click on Create or type Return. The first of the two radio buttons ( Retain subfile structure ) is selected by default, making the records appear in the same subfiles as before. Any subsidiary indexes , therefore, will still be valid. If you deselect this radio button with ADJUST (i.e. have neither button selected) then all the records will be placed in the currently selected subfile and you will need to rebuild your subsidiary indexes. Selecting the second button ( Restore deleted records ) also places the records in the current subfile, but has the additional effect of recovering deleted records, i.e. records which are still lurking in the Database but aren t in the primary key index . You would need to use this option when the primary key index is lost or corrupted. If the PrimaryKey file is actually missing both radio buttons are shaded in order to enforce this mode. Note that you cannot recover deleted records if the Blank record on deletion button is set in the Preferences window: the data has really gone for good in this case. Altering the record format Some changes can be made without rebuilding the database. How much you re allowed to do depends on how old the database is since a change in the structure of the Database file, permitting far more latitude in changing the structure, came into effect with Powerbase v.8.21 . Databases created with earlier versions use filetype for the Database file and a characteristic icon (also used for the file) which used to bear a large . Databases created (or reformatted) with v.8.21 or later use a plain text file for Database . The old icon is retained for the file and, since this is now its only use, the letter has been changed from . (This means that old Database files viewed with a recent version of Powerbase also show an If the second choice on the Utilities submenu is Adjust format the database is an old one and only minor changes are possible by this method. Thus you can t delete fields or add new ones. You re-position fields and change descriptors and tags. You can t alter the data length s (except in one case; see below), but you can alter the visible lengths of data fields by ADJUST-dragging the edge of the data icon. You can also change the field type within its class , e.g. an Alphanumeric field could be changed to a Numeric one since both belong to the Editable class, or you could change a 2-state Check-box to a 3-state one, but you couldn t change either into a Calculated field. The only type of field where the data-length may be changed is a Scrollable list. Although classed separately from External field types it has much in common with them in that the data for each record is stored in a separate file. Editing the number of columns, the column widths and the number of visible rows is allowed even for old-type databases. If the menu choice is Alter format the database structure is the more recent one. In addition to the changes described above you can add fields of the following types: All tool-pane buttons All extra buttons, including those which open directories, run files or insert calculated results Textual labels. i.e. fields which consist of a descriptor only All External field-types except Remote Logos Scrollable lists When you choose Adjust/Alter format the Field definition window with the layout grid is displayed. Double-click on a field to edit it. Many of the options will be shaded, especially if the choice was Adjust format (old style database). When you have finished making the changes choose Quit design from the menu. Rebuilding the database This is more drastic, but it is not always possible to foresee future needs and you may have to introduce a new field or lengthen an existing one. That involves rebuilding the whole database. Powerbase actually builds a completely new database under another name, leaving your original database unchanged in case anything goes wrong, so this is a very safe procedure which you needn t be afraid to experiment with. Choose Rebuild database (old-style databases show New record format in the menu) . The Reformat database window will appear. Several options are now available: Enter the new database name and drag the database sprite to a filer window. You will then be offered the record layout for editing, as when creating a new database. Make the required modifications then choose Quit design from the menu. This is the recommended method. If you already have a file in the new format drop it onto the Reformat window where its name will appear. Enter the new database name and drag the sprite to a filer window. Create a new database shell (which must have a different name from the one to be reformatted) and copy your existing Form file into it. Open this database , edit the layout and save the file. Open the database, call up the Reformat window and drop the new one onto it. Its pathname will appear in the window. Finally click on Reformat . This was the method used in versions of Powerbase prior to 7.52 and, although it still works with the latest version, it should be regarded as obsolete. Data from a field in the old database will be copied to a field in the new one with the same tag. The new database must therefore retain the same as the old for data which is to be common to both. A tag present in the old database but not in the new is assumed to refer to a deleted field and the associated data is not transferred. A field present in both databases but with a shorter defined length in the new one will have its data truncated if necessary. A tag present in the new database but not in the old is assumed to be a new field and will be left blank. Before proceeding Powerbase will inform you of any likely data loss due to deleted or shortened fields and ask you if you wish to proceed. When the process is complete you will be left with the new database open. Links to validation tables, calculation formulae, and indexes are preserved unless a relevant field was omitted from the new record. Merely to convert old-style databases to the newer format choose New database format , drag the icon from the Reformat window to a suitable directory and choose Quit design without making any changes. Merging two databases Two databases may be merged provided they have identical record structures. With the first database open, select Merge database and drag the second database to the displayed window. The pathnames of both databases can now be seen. The pathname of the open database is duplicated in a writable icon labelled Save as . If you accept this default the data from the second database will be merged into the open one. You can, however, change this name and drag the database icon to a directory window. The merged data will then go into a completely new database leaving both source databases as they were. When you click on Merge database Powerbase will check that the two record formats are identical and report an error if they aren t. An option button determines the action taken with regard to Sequence number fields (see also 8.4.1 Two radio buttons are also present, as in the dialogue box for rebuilding the primary key , and their functions are essentially the same (see ). Having the first button selected merges records into the same subfiles as those they occupy in the second database. Having neither selected merges records into the current subfile of the first database, regardless of where they are in the second database. Having the second button selected does the same, but also imports records which were deleted in the second database. Changing the Database Length The number of available records in a database may be increased or decreased. Change length leads to a window with two writable icons. The first specifies the new database length, the other determines the number of records by which the database will be lengthened each time it becomes full. If this value is zero no automatic lengthening occurs; a warning is displayed instead. You will only be allowed to shorten a database if the surplus records have never been used or have been blanked on deletion. Otherwise you can only get rid of them by exporting all the current records as a subset (see 13.2) Inspecting and balancing index trees Print index lets you examine the structure of the current index to find out how many are present in each level. The ideal numbers in a perfectly- balance index tree are 1, 2, 4, 8, 16, 32, 64, 128 etc. (i.e. powers of 2, beginning with 2 A submenu gives you a number of options for printing the index details. You may print only the numbers of nodes in each level (with the ideal values listed above for comparison) or print the actual , positioned according to the level they occupy in the tree. The tree is printed sideways with a choice of two formats, Root first Symmetrical . The data may be printed for all subfiles of the current key or for the current subfile only. Output is to a screen window from which it may be saved in the same way as any other report. If you have printed the themselves then double-clicking with SELECT on any key will retrieve the associated database record. If the tree is very un balance d and, especially, if there is an enormous number of levels with only 1 or 2 in each, you are advised to balance it using Balance index Ctrl B . The totals in each level are displayed after balancing. If you initiate the process with Ctrl Shift B the full tree structure is printed after balancing. It is also possible to make Powerbase balance the index automatically at regular intervals. To turn on auto-balancing choose Preferences from the iconbar menu. Select the Balance every <n> records button, placing your chosen value of in the writable icon provided, then click on Accept Auto-balancing is most likely to be useful when a large number of records are being entered in primary-key order. This will happen if you are entering data from a lot of forms in alphabetical order of name where name is the primary key . It is also very likely to happen when importing CSV files as these are often ordered according to the data item which becomes the primary key field of the database record. Useful database details Powerbase menu is headed by the Information entry which displays a window giving the numbers of records available and used, and the way the latter are distributed amongst the subfiles. (The subfile names are on action buttons which may be used to move to a chosen subfile.) A button enables the information in this window to be saved as a text file. Database details on the iconbar Utilities menu has its own submenu with two choices. Record data displays the same information as described above with a couple of useful additions: the record length in bytes and the number of fields in the record. Field data lists the field number, class, type, data-length, tag and descriptor for each field in the record. If you move the Report window out of the way so that the record window is visible you will find that double-clicking on a line in the report places the pointer over the middle of the field. This can be useful when finding your way around a complex record where many fields are without descriptors. Finding duplicate primary keys Wherever possible a primary key should be chosen so as to be unique. Where duplication of the primary key might occur the designer of the database can decide either to allow or forbid it (see 11.2.1 ). If duplicate are permitted it is sometimes useful to have a list of them. Such a list is created by Find duplicates and may be saved as a text file. 10.9 Libraries Ch 15 describes how non-standard features can be added to a database by means of a customisation library . Where possible all customised databases should occupy the directory specified by CustDir !Powerbase.Resources.Config . The Libraries choice on the Utilities submenu will list all the customisation libraries known to Powerbase at that time. The entry is shaded if none were found. Double-clicking on a line of the report opens the relevant Customisation directory for examination and editing. icking 0D - * A poor choice of key, with many duplications of just a few values, inevitably leads to a tree with many levels in which each level is sparsely populated. An attempt to balance the tree under such circumstances may give very little improvement. Ch 11 Passwords and related matters Levels of protection Powerbase provides three levels of password protection . None of the sample databases uses password s, so you may examine the password -setting dialogue box which is accessed by choosing Set passwords from the Miscellaneous submenu. If passwords have been defined then an attempt to open the database leads to a request for a password. This is not readable as you type it in; all the characters are replaced by hyphens. If Powerbase does not recognize the supplied password it beeps and prints an error message in red then prompts for the password to be re-entered. After three failed attempts Powerbase will shut down. The lowest password level is . It is intended to let users examine the database but otherwise keep them out of mischief. A user opening a database with the Read password will be allowed to browse, search and print reports (unless debarred as described in ), but not much else. In fact it will be found impossible to place the caret in a field. The function keys won t work, many of the buttons on the tool-pane will be inactive and some menu items will be shaded. In particular, the whole of the Miscellaneous submenu is unavailable, thus preventing the user from getting at the other passwords! The next level, Read/write , allows more operations. Everything on the tool-pane is allowed, including altering, creating and deleting records. You may create indexes and create and edit validation tables. What you cannot do is change passwords or alter the size and structure of the database by means of options on the iconbar Utilities menu. The top level is Manager . At this level you are allowed to set and alter passwords, as well as use all the iconbar menu options. Passwords may be up to 10 characters long and are normally case-specific. Note that when you are setting the two lower ones they can be read but the Manager password appears even here as a row of hyphens. Take care not to forget it! If no passwords at all are set you have full Manager rights so you may ignore the password facility altogether if you are the only user of the database. If you decide to use passwords you might not wish to use all three levels of protection and don t need to do so. You may set a Manager level password only, leaving the other two blank. When you attempt to open the database in such circumstances the password-entry window appears because Powerbase t read your mind and doesn t know whether you re the Manager and wish to enter your password or whether you re a lower-level user being given limited access without needing a password. If the latter you would simply click on Open or type Return, thus entering a null password. This would give you Write access. If you set the Manager Read/write passwords then entering a null password would give Read access. It obviously makes no sense to set a low-level password and leave a higher level one blank! Powerbase prevents you from doing this in the following way. If there is a Read password but no Write password Powerbase makes the and Write passwords the same. Similarly, if there is a Write password and no Manager password these two are made the same. This means that setting only the Read password makes all three identical. You are then admitted to Manager level when you enter this password otherwise you could never get Manager level access again! The rule is that when two or more passwords are identical you are always given the higher level access when using that password. 11.1.1 Individual I.D.s and passwords Access button makes it possible to assign a user an and a personal password which allows access at one of the three levels described above. This gives even greater security by limiting access to those people who appear on a concealed list. It does not seem appropriate, however, to describe the creation of such an access-control list in a manual for the general reader and the Access button is therefore shaded on copies of Powerbase as distributed. Anyone wishing to set up a secure database for use by a list of specified users is welcome to write for information to Powerbase Support, explaining their requirements. If a database is protected by an access-control list the password-entry window which appears when you attempt to open the database will display two writable icons instead of one. The first is for the user s I.D., the second is for the password . Enter both and click on . A an error in either the I.D. or the password will result in access being denied without informing the user which input contained the error. Options selected from the password -setting window As well as the icons for the password s you will see eight option buttons which may be used to enable or disable certain features of Powerbase . They are placed here so that they may only be altered by someone who has Manager rights as that is the only access level which allows you to open this window. All the buttons except the last ( Log changes ) are selected by default so that all the named features are enabled. If you deselect any of these buttons the feature concerned will cease to operate when you click on and will remain inoperative every time the database is opened unless you set the button to re-enable it. The first four buttons determine the availability of the tool-pane keypad , the equivalent function keys , the main menu and the iconbar menu . All these features are enabled by default but you may disable some or all of them in order to restrict the actions of other users of the database. This subject is treated fully in Ch 14 The fifth button determines whether or not the exporting of CSV files and subset databases is allowed and the sixth does the same with regard to report printing 11.2.1 Duplicate primary keys The seventh button determines whether the database is allowed to have duplicated primary keys or not. As mentioned elsewhere (see 4.5.2 ) the primary key should, ideally, be unique for each record. Sometimes the very nature of the data will make it so but, if this is not the case, you can enforce primary key uniqueness by deselecting this button. Any attempt to enter a record which would have a primary key identical to one which already exists will then result in an error message and the new record will not be placed in the file. If you decide to allow duplicate primary keys (and this is the default, remember) you might still want to be warned that such a key is about to be created and a button on the Preferences window allows you to enable or disable this feature. You can also make Powerbase print a list of duplicate primary keys (see The above refers only to the primary . Other, subsidiary which are generated when you create an index on some other field (see ) are subject to these restrictions and may be repeated many times. Logging database changes Selecting the last option button ( Log changes ) and clicking on opens a log inside the database directory. Powerbase will then keep a record of when the database is opened and closed and details of changes made to the records. The following changes are logged, details in brackets showing what is recorded: creation of a new record (record number & primary key ) deletion of a record (record number & primary key shifting a record to another subfile (as above, plus old and new subfiles changing a field in a record (as above, plus old and new contents) changing a field in a group of records (change, subfile and search formula) creating an index (name of new index ) balancing an index (name of index concerned) changing the primary key (former structure of key) reformatting the database (fact only no details) merging data from another database (source of merged records) importing CSV-type data (source of imported data) As long as the Log changes button remains selected the log file will be opened whenever the database is opened. The title of the database is recorded and the password level at which entry was achieved. This takes the form of a number with the following meaning: password required, 1 read only, 2 read/write, 3 manager The date and time are also recorded. When you close the database the date and time are recorded again and the log file closed. The log file is a plain text file and is always called . Although the default location for the file is inside the database directory you may start a log somewhere else by dragging the small text-file icon next to the Log changes button to a directory display. When you release the mouse button the password window is closed just as if you d clicked and logging is enabled. (It doesn t matter whether you have selected the option button or not in this case; Powerbase will select it for you.) An advantage of this method is that you may perform the same drag to the same directory from many different databases and have them all use the same log. The !Scrap application is a possible location for a general log of this kind. loads the log file into an editor for inspection. only, Ch 12 Script Files Powerbase incorporates a script language which enables you to control many features from a script . Using script les you can automate jobs which need to be done frequently, print jobs and mail-merges being the most obvious examples. Script are plain text les (created with StrongEd etc.) and are executed by dragging them to the Powerbase record window. All commands in a script le must be in upper-case, preceded by , and may be followed by parameters (which are not case-sensitive), separated from the command by a single space. A description of all the commands follows. Parameters in square brackets are optional: in actual use the brackets are omitted. Where commands simply control the status of options buttons (e.g. !CASE, !UPPER) the parameter is either ON or OFF. The !SCRIPT command Since scripts are just text les there has to be a way for Powerbase to recognise them. All script les must therefore start with a line beginning with the command !SCRIPT . (or ! SCRIPT POWERBASE if you want to be more speci c, but !SCRIPT by itself is suf cient). You may use ! SCRIPT END as the last line of the le but this is optional since the script will terminate anyway when the end of the le is reached. You can make the command chain another script le by following it with the name of the new le, i.e. by using the form ! SCRIPT lename> (see 12.1.1 ). When a script le is dropped onto the record window, or started by means of a button on the record window, its name will be displayed with a request for con rmation that the le is to be executed. To suppress this request use !SCRIPT QUIET as the rst line. If the PrintRes directory contains a script le called !Script it will be executed automatically when the database is opened. 12.1.1 Filenames in script commands Many of the following commands take a lename as a parameter. If you just give a leafname Powerbase will look for it in the database PrintRes directory. If it is stored anywhere else either the full pathname or a relative pathname must be supplied. Where a script makes use of Selection, Print options, Query or other les which are relevant to only one database, the best policy is to store all these and the script le itself in the database PrintRes directory and link the script le to a button (see 4.2.13 ) on the record window. Dropping the script le onto the button will store the leafname of the le (if it is in PrintRes ) or either the absolute or relative pathname (see 4.2.9 ) if it is elsewhere. Clicking the Run le button with ADJUST will display the stored pathname. When you click SELECT on a Run le button to which a text le is attached, Powerbase examines the rst line of the le. If this begins with !SCRIPT the le is executed as a script. If not the le is loaded for editing. To edit a script le linked to such a button click with Shift SELECT. Commands used for reporting Reporting is used here in its broadest sense for any operation capable of producing a le containing record data. Thus, creating CSV les and exporting single records are included. !SELECTION is used to select elds for inclusion in a report. If a lename is given as a parameter Powerbase looks for a print Selection le of that name and loads it, just as if you had double-clicked on it or dragged it to the record window. (See note above for the interpretation of lenames.) Alternatively, you may supply a comma-separated list of the tags of the required elds and may include the record number, key and sub le number by placing the words RECORD, KEY and SUBFILE, respectively, in the list. If you supply no parameter at all the current selection will be cleared. !SUBFILES <n>[,<n1>,<n2>...] selects the sub le(s) which will be searched in order to create a report. !CASE turns the Case button on the Query panel ON and OFF. !QUERY lename\]< search formula is used to print a report. It returns the same result as entering a search formula in the Match window and clicking Print . If you rst issue a !DESTINATION FILE command you can use a list of !QUERY commands to create a batch of reports as text les. If you omit the optional lename each le will be created in PrintJobs using the search formula (or part of it) as the lename. If you supply a name containing $ it is assumed to be a full pathname and will be used to create the le. Any other name causes the le to be created in PrintJobs or in a subdirectory (which must exist) of Print Jobs. Note the backslash between the lename and search formula; this tells Powerbase where the one ends and the other begins. !CASE also affects the behaviour of this command if issued beforehand. Instead of the search formula you may supply the name of a saved Query le. If this le is in PrintRes only the leafname need be given as noted in 12.1.1 An error message is produced if an attempt is made to execute !QUERY with the format set to Table or Label when Destination has not been set to Printer. !PRINT prints the displayed record as a single-record report, just as if you typed Shift-Print or Shift SELECT clicked the Print button on the Match window. !SAVE lename> may be used to save a le which has been created in a window by a !QUERY command. When issued without lename it uses whatever name the !QUERY command would use if the destination were File instead of Window, i.e. a name given in the !QUERY command or one based on the search formula, but any supplied name overrides this. !SAVE is also used to save single-record reports created with !PRINT. If no lename is given the le will be saved in PrintJobs under a name derived from the primary key or, if is supplied as a parameter, as RecXXX, where XXX is the record number !DUMP < lename> has an effect similar to that of Ctrl-dragging with SELECT on the record window. It saves all exportable elds of the record as a text- le in PrintJobs. Filenames are as for !SAVE , i.e. derived from primary key if no parameter is given and RecXXX if parameter is lename\]<search formula> is similar to !QUERY but produces a CSV le instead. Once again, the name of a Query le may be supplied instead of a search formula. The CSV option settings are observed. A prior !DESTINATION command is unnecessary in this case. !INCLUDE !EXCLUDE !CLEAR have the same syntax and may be treated together. They have the same effects as the controls on the Mark pane attached to the record window. !INCLUDE <key>[,<key>,<key>...] has exactly the same effect as ticking the check-box on the Mark pane for records with the associated primary keys. !EXCLUDE has the same effect as marking with a cross, which is what happens when the alternative option from the pane s pop-up menu is chosen. !CLEAR selectively clears marks or, issued with no parameters, clears all marks. As an alternative to a comma-separated list of keys you may supply a lename. The le should be a plain text le with one primary key on each line. (See 12.1.1 for the interpretation of lenames.) Setting print options !PRINTOPTS lename> loads a Print options le, setting the options just as if the le had been double-clicked or dragged to the record window. Remember that a Print options le contains all the settings for both the Print options Printer setup windows. Without a lename it sets the default options. !DESTINATION <string> where <string> is Window, File or Printer sets the destination for report printing (see ). If File is used the report is saved in PrintJobs unless a subsequent !QUERY command supplies an alternative pathname (see above). !FORMAT <string> where <string> is Horizontal Vertical , Label or Table sets the print format. If any other parameter is used, or !FORMAT is issued by itself, the Horizontal format is used. !EXPAND selects the button which causes elds to be expanded by reference to a linked validation table . !EXPAND OFF (or just !EXPAND by itself) deselects the button. !DATE turns date and time stamping ON and OFF. !UPPER forces upper-case printing ON, normal upper/lower case printing otherwise. !HEADER, !FOOTER determine whether header/footer information (see 3.10.2 ) is printed on reports. !FIRSTPAGE when followed by ON causes the header to appear on the rst page only. (Footer information only appears on the nal page anyway.) N.B. This command was formerly called !FIRST, a name now used for a totally different command. !SHRINK determines whether surplus white space is removed from between the columns of reports in Horizontal format. !HEADINGS causes descriptors to be used as eld identi ers in a report. !HEADINGS T, or without any parameter, causes to be used. !TITLE <string> uses <string> as a title on subsequently printed reports. !PAGE sets the page length for reports to lines (see 3.10.2 !SPACER <string> speci es the string to be used to separate columns of printed data in reports using Horizontal Table format (see 3.10.2 !TEXTWIDTH speci es the maximum width in characters for Memo and Text block elds in reports. !SORT speci es a column on which the report is to be sorted before printing. The tag of a eld included in the report may be speci ed instead of a column number. The !SORT must appear before !QUERY in the script le. It cannot be used when the output destination is File. Setting printer setup options !ORIENTATION <orientation> changes the paper orientation for hard-copy printing The default is portrait upright , the alternative is landscape sideways !PRINTCOLUMNS sets the number of columns used in printing a report when the Destination is Printer. n may be 2, 3 or 4. Anything else (including 0) is interpreted as 1. !HEADERFONT !BODYFONT <fontname> set the fonts for headers and footers and for body text in hard-copy printing. Names should be of the form Homerton.Medium, Trinity.Bold.Italic etc. The font must, of course, be known to the font-manager. !FONTSIZE sets the size, in points, of the fonts used for hard-copy printing. !LMARGIN !RMARGIN !TMARGIN !BMARGIN set the print margins for hard-copy printing. The units default to mm unless speci ed by appending mm, in or pt to the number. !PMARGINS cancels the margin settings in the Printer Setup window and uses the printer driver s default margins instead. !LINESPACE sets the spacing between the baseline of one line of printed text and the next as a percentage of the font size. The default is 120%. For Table format 150% looks neater. !TABLE <columns>,<width>,<rows> sets up the number and width of blank columns, and the number of blank rows at the end of the report, in Table format. Column width is in mm but may be changed as for margins (see above). You need not specify all the parameters; current values will be used if any are omitted. You may, for example, specify 6 blank rows and nothing else by using !LABEL ,,6. Note the commas. !LABEL <n>,<w>,<h>,<str1>,<str2>,<s>,<su>,<bl>,ON/OFF determines the setup for label-printing where: n=number of labels across the page, w and h are the width and height of label (units may be speci ed as for margins and column widths), str1 and str2 are the optional rst and last lines and s=number of label at which printing on rst page is to start. su and bl are eld tags. If eld bl is blank then eld su will be printed instead. The nal parameter speci es whether the primary key is to be printed on the label. As with !TABLE you need only specify the values you wish to change, but watch those commas! !COPIES speci es the number of copies to be printed. Commands used mainly for editing !STARTAT <tag> sets the eld at which editing begins and places the caret in that eld. If no parameter is given, or if the designated eld doesn t exist or isn t writable, editing starts at the rst writable !FIRST !LAST !NEXT !PREVIOUS may be used to move about the current sub le and display records. The meanings of the commands are pretty obvious. !DELETE <key>[,<key>,<key>...] deletes record(s) having the supplied primary key(s). As an alternative to the key or key-list you may supply the name of a text- le containing the required keys, one to a line. The lename will be interpreted as described above for !SCRIPT < lename>. The command can delete records in any or all of the six sub les, not just the current sub !INSERT is the counterpart of !DELETE. Useful in similar circumstances. In this case the command must occupy a line by itself and be followed by the record to insert, one eld to a line. It is the user s responsibility to ensure that the lines are not too long for the database elds into which they are placed and that the number of lines following each !INSERT is the same as the number of elds in the record. Don t forget to leave blank lines for empty elds! Records will be inserted into the currently-selected sub !MOVE has the same syntax as !DELETE but moves records from their present sub le to the next in sequence, regardless of the currently-displayed sub le; e.g. a record in sub le 2 will be moved to sub le 3. !CHANGE eld tag>,<old contents>,<new contents>[,<search formula>] allows global changes to be made. The command functions like the Global changes menu choice (see 2.5.5 !FILTER <search formula> has the same effect as entering a search formula in the Filter window. The Filter window isn t opened but the Filter button on the tool-pane (or on the record screen if there s a Filter button there) is turned on and the rst matching record is displayed. !FILTER on its own cancels the lter. !FILTEROPEN <search formula> is identical to the above but also opens the Filter window. !FIND <key> locates and displays a record using the key in the currently-selected index. <index name or number> where the speci ed index appears on the menu of loaded indexes will make that the currently active index. If no parameter is given the primary key index is selected. !INDEX <tag> constructs an index on the eld whose tag is speci ed. Used in this, simplest, way the number of characters, word-number and position (see 4.5.1 ) default to maximum eld-length, 1 and L respectively. You may specify these extra details by placing them after the tag and separating them with colons , i.e. <tag>:<chars>:<word>:<pos> speci es all four parameters. A key based on more than one eld (or using the same eld more than once) may also be de ned, each individual eld-speci cation being separated from the next by a semicolon . e.g. the key based on surname and forename described in section 4.5.2 (p.42) would be de ned by: !INDEX NAME:4:1:L;NAME:1:2:L A further colon may be followed by a string consisting of some or all of the letters COPJ to select the icons for ase speci mit null keys, ad with spaces and ustify numbers. A single !INDEX command may create several indexes, each in turn becoming the currently-active index. Commas are used to separate individual index speci cations. Using the Elements database, for example, (and specifying the indexes in the simplest possible way) the command: !INDEX Z,GP would index rst on the Atomic number (Z), then on the Group eld (GP) with the Z index active . The database is left with GP as the active index and a report of the whole database shows the records ordered by Group but, within each group, the records are in order of Atomic number; a sort within a sort. Powerbase remembers the names of the indexes created by the most recent !INDEX command and will delete them if you issue !INDEX with no parameters very useful for temporary indexes. Repeating operations using a loop !LOOP <loop-counter or terminating condition> initiates a loop. If a positive number is entered as the parameter it is assumed to be the initial value of the loop-counter. The value is decremented every time !ENDLOOP is reached. !ENDLOOP marks the end of a loop. The terminating condition or loop-counter is tested whenever this command is reached and the loop terminates if either the condition is TRUE or the counter is zero. A loop may be terminated in four different ways: (1) When the loop-counter becomes zero. (2) By supplying MOUSE S MOUSE A as the parameter to !LOOP and placing !WAIT (see below) inside the body of the loop. In the rst case the loop will terminate when you click SELECT, in the second when you click ADJUST. (3) You may supply any single word of your choice as a parameter and include an input-requesting command (see ) in the loop body. Typing the speci ed word into the input icon will terminate the loop. The word must consist of letters only spaces, numerals and punctuation are not allowed but it isn t case-sensitive. (4) By supplying a search formula as the parameter. This is only useful if records are being scanned within the loop, e.g. by a !NEXT command. The rst record to match the search formula will terminate the loop. When a preceding !DATA command runs out of data. See !DATA (below) If no parameter is given the terminating condition is set to TRUE so that the loop terminates after one iteration. This reduces the likelihood of the program being trapped in an endless loop, although this is still possible. By using the input-requesting facility (see ) inside a loop you can now include command sequences such as: !LOOP stop !FIND Key to look for? !PRINT !SAVE !ENDLOOP The script will keep requesting keys, looking up the records, creating single-record reports and saving them as RecXXX, RecYYY etc. until the user types !PAUSE pauses execution of a script le for <n> centiseconds. !WAIT changes the pointer to a representation of the mouse with its SELECT button highlighted. Execution will continue only when a mouse button is clicked or a key pressed on the keyboard. !DATA <param> [,<param>,...] is especially useful inside a loop. If, in a subsequent command, READ is supplied as a parameter it will be replaced by the rst parameter in the comma-separated list following !DATA. When a command next has READ as a parameter it will be replaced by the next item in the !DATA list and so on until the list is exhausted. This is suf cient to terminate the loop; there is no need to supply an explicit terminating condition. The following script, based on the Elements database, will clarify the situation. !DESTINATION FILE !DATA GP=1,GP=2,GP=3 !LOOP !QUERY READ !ENDLOOP rst time around the loop the !QUERY READ command will be changed to !QUERY GP=1. Since the print Destination is File the resulting report will be created in PrintJobs under the default name GP=1. The second time round the command becomes !QUERY GP=2 and the third time it becomes !QUERY GP=3. The data list is then exhausted and the loop terminates. A search formula may contain commas, in which case the formula must be in quotes as in the example below. Note that the !DATA line must precede any command which READs from it. If you wish to provide your own names for the reports these can also be included in the !DATA list, alternating with the search formulae: !DESTINATION FILE !DATA MainGps,"GP=0,1,2,3,4,5,6,7",Transition,GP=T !LOOP !QUERY READ\READ !ENDLOOP On the rst cycle of the loop the READ before the backslash is replaced by MainGps and the READ after the backslash by "GP=0,1,2,3,4,5,6,7". See syntax of !QUERY ( 12.2) regarding inclusion of a lename with the command. Transition and GP=T replace the READs the next time. Be careful not to make lines too long when using !DATA. The maximum allowed length of the whole line is 254 characters. You may, however, use a block of up to ten consecutive !DATA lines and read them all in one loop. If you need more than this you must place them in a separate block after the !ENDLOOP and then initiate another loop to read them. Other commands !IMPRESSION is used in conjunction with !QUERY to generate a report in text- le format, allows you to insert Impression commands at the start of the text- le. A script may, for example, execute a series of !QUERY commands and the resulting les are to be all selected together and dragged into an Impression document. To make each le go into a new frame you need to make each begin with {nextframe}. This can be achieved by placing the script command !IMPRESSION {nextframe} before the rst !QUERY. You do not, however, want {nextframe} to begin the le created, or the Impression document will have a blank frame on its rst page. To suppress the effect on the le use: !IMPRESSION {nextframe} Not rst. There must be a space between the and the , but the string isn t case sensitive. !OBEY <string> can be made to do anything which a *command can do. e.g. !OBEY Delete < lename> will delete the named le. !OBEY < lename> will attempt to *Run the le. By supplying the pathname of a Powerbase database you can close the existing database and open a new one. !MAILMERGE lename> performs a mail-merge with Impression Ovation Pro . The lename is that of the document into which the data is merged. A !FILTER command, issued before the !MAILMERGE, will restrict the merge to records which match the search formula. !COMMENT <string> may be used to insert comments in a script le. The rest of the line is ignored. !MESSAGE <string> will display a user-de ned message in a small window to keep the user informed of what s happening during execution of a script. A numeral in the range 1-9 attached to the end of the message may be used to specify how long the message remains on screen. If no number is given the message remains until !MESSAGE is issued on its own. This closes the message window. The window is normally centred on the screen, which can be annoying when used in conjunction with the input-requesting facility. This also centres its window so that one window covers the other. Pre xing the message with a number, followed by a comma, moves the window up or down. e.g. 50 will place it just above the input-request window, 150 will place it below. !DISPLAY <tag>,<num> enables auto-display of External elds and initiates auto-scan of the sub le as if the button had been pressed (see 4.2.10 ). The second parameter determines the interval between records: the bigger the number the longer the delay. It is not, however, an actual time. A value of 10 will ip through the records quite rapidly; 100 is much slower. If the numeric parameter is omitted the auto-scan is stopped and the auto-display feature is disabled. Requesting user input from script This is best illustrated with an example. !FILTER GP=1 will use GP=1 as a search formula. However, !FILTER Search formula? behaves quite differently. A small window pops up saying Search formula? with a writable icon into which you enter the formula then click or type Return. This applies to all script commands which take a parameter. Instead of supplying the parameter itself in the script, supply a prompt followed by a question mark and the parameter and will be requested and used with the command. Why would you want to do this? You might use a script to print a report in Table format but not always want the same number of blank columns and rows. You can request these with !TABLE Columns?,,Extra rows?,. Note the double comma (column width is not changed) and the comma after Powerbase checks the parameter string for a concluding which, if found, causes the whole string to be treated as a prompt. This is ne for commands like !TITLE which take only one parameter but those which take multiple parameters need the concluding comma. When requesting input to the !FORMAT command you need only enter the initial letter H, V, T or L as appropriate. H - 7 0` - u - 7 0() - 0 a - 0xT - 0@& - 08^ - Ch 13 Subset databases Subset databases are fully-working Powerbase databases with exactly the same structure as the main database but containing only a selection of its records. Creating a subset Creating a subset is extremely simple. Choose Export subset from the main menu and you will see a window strongly resembling the one used to export CSV files . It features our old friend the Query panel into which you type a search formula which determines the records to be exported as a subset It is then only necessary to enter a suitable filename and drag the database icon to a filer window. The default pathname creates the new database as Subset in the same directory as the parent database. You can accept this default by just clicking on Export or typing Return. Records are exported from the currently-selected subfile and will be placed in the corresponding subfile of the subset database. You can export records from several subfiles, not just the current subfile, and they will be in the same subfiles in the subset database. On opening the new database you will find that it functions exactly like the original but contains only the selected records. It is made just large enough to contain the selected records so you will need to increase its length (see ) if you intend to add any further records. Using a subset to shorten a database A database which has undergone a lot of deletions might have blank records scattered at random throughout its Database file and unused keys scattered at random throughout its indexes. If a database has 100 available records of which only 50 are in use you might want to get rid of the surplus records but aren t able to do so by the simple shortening procedure described in Ch 10.5 because that only allows you to lop off the of the database. The amount by which the database can be shortened is often much less than the number of in-use records would suggest: in extreme cases you might not be able to shorten it at all by this method. (Don t be afraid to try, however. Powerbase t let you butcher your database; it just tells you you can t do it.) The solution to the problem is to export the records as a subset. Simply follow the instructions in , with all occupied subfiles selected in the Query panel, but don t type anything into the query panel. The resulting subset will contain the 50 in-use records and no extras at all. Ch 14 User choices This chapter describes things you can do to influence the way Powerbase looks and functions. Preferences The largest group of user-defined choices is that controlled by the Preferences window, which is opened from the iconbar menu. Several of these choices have been mentioned in previous chapters but a full description of the window is provide here for completeness. 14.1.1 Control This refers to the framed portion of the Preferences halfway down the right-hand side, and we will begin with this because the settings chosen have an important bearing on what you see on opening a database and how you move around and edit records. The ve buttons in this section have an instant effect so you can readily see what they do. If the tool-pane/keypad has been disabled from the Passwords window (see ) then all ve buttons are shaded. The three radio buttons enable selection of the tool-pane or the alternative keypad (see above) or neither. In the last case there should be control buttons on the record window. New sprites is shaded when the keypad selected, but can enable a set of alternative symbols (with left right-pointing arrows) for the tool-pane or record window where some buttons, such as Single-step, do duty for two keypad buttons. Rev. mouse reverses the action of the left and right mouse buttons. It only becomes unshaded when New sprites are selected since it then seems very logical to click the left button to produce the action indicated by the left-pointing arrow and the right button for the action indicated by the right-pointing arrow. e.g. on the Single step button ADJUST would display the next record and SELECT the previous record the opposite to normal. The blue sprite on the Single step button is replaced by a red one as a visual indication that the mouse-button actions are reversed. The keypad has button-pairs for several functions which, on the tool-pane, are served by single buttons clicked with SELECT and ADJUST, e.g. there is both a next record and previous record button instead of the dual-function Single step button on the tool-pane. Even so, clicking ADJUST on the paired buttons still reverses the action of SELECT. The most frequently used buttons on the keypad are those on the top two rows, and clicking the toggle-size icon (top right corner) reduces the keypad to these rows only. Note that there is a close icon (top left). If you do close the keypad it can be re-opened with Show keypad on the main menu (or with the key). 14.1.2 Separators in date and time fields By default Date fields have the format dd-mm-yy or dd-mm-yyyy, i.e. they use a hyphen as a separator. Time fields have the form hh:mm:ss, i.e the separator is a colon. These defaults may be changed by entering your preferred separators in the two writable icons in the Separators section at the top of the Preferences window and clicking Accept. Note that when entering dates or times into these field types you may actually type any non-numeric character you wish as a separator. On moving to another field Powerbase will re-format the date or time to use the separator specified in Preferences. 14.1.3 Wild-cards The use of wild-card characters is described in 2.5.5 and 3.5.3 . By default represents a single character and a group of characters which need not be matched. You may change either or both wild-card characters if you wish in the same way as described above for date and time separators. 14.1.4 Option buttons The descriptions given below refer to the action taken when the buttons are selected. Record edits as CSV file causes a CSV file called NewDataXX to be created in PrintJobs . New and edited records are saved in this file which may then be used to enter data into another database. XX is a 2-digit number which is incremented each time the database is opened so that the results of successive editing sessions are saved as NewData01, NewData02 etc. Launch new copy causes another copy of Powerbase to be loaded when you double-click on a database, instead of closing the current database. This can be useful if you want to access several databases at once, as one copy of Powerbase only supports the use of a single database. If there is a dormant copy of Powerbase on the iconbar (i.e. one with No data under its icon) then double-clicking a database sends it to that copy rather than launching a new one. With the button OFF double-clicking on a database closes down an already-open database before opening the new one. Even then you can load multiple copies of Powerbase by double-clicking on Powerbase itself and then open a different database in each by dragging the databases to the icons. Case-sensitive queries makes operations involving the Query panel take account of the case of letters. Recalculate on opening causes Powerbase to scan the whole file when the database is first opened, updating any Computed field s (see and ) whose associated formulae make use of the system variable TIME$. Those Computed fields which are analogous to time-related Stamp fields (see 4.2.10 ) are also updated. With the button deselected the contents of such fields are recalculated only when a record is displayed, which might not be good enough where, for example, TIME$ is used in a User function (see ) to keep track of people s current ages. Blank record on deletion causes the relevant record to be over-written with a blank record when its key is deleted from the primary key index. With the button deselected deleting a record from the database merely removes all references to its key from the index, leaving the actual record data untouched in the Database . Although such deleted records will be overwritten when new records are entered, the situation may be deemed unsatisfactory from a security point of view since the deleted records can be examined by simply loading Database into Keep descriptor with data affects the moving of fields during record design, making the descriptor jump to its normal position to the left of the data field as soon as you release the mouse button after dragging the latter. As noted in 4.2.1 , the descriptor may be moved independently after the data field has been positioned but, even so, some users find this repositioning of the descriptor a nuisance and like to turn it off. Validate input enables data validation (see ). If you ever find yourself unable to escape from a field linked to a validation table you can bring up the Preferences window, deselect this button and try again. You can also toggle validation ON and OFF with Shift-F9 . The Table button on the tool-pane is shaded when validation is disabled. Display linked table data displays a window showing data from the relevant validation table row every time the caret enters a field with a link to a validation table. This is either useful or infuriating, depending on your point of view. If turned off you can still call up the window for a specific field by double-clicking on the field with SELECT (see Warn of external deletion displays a message warning that you are about to delete the file linked to an External field (see 2.6.3 Warn of duplicates The creation of records with identical primary keys can be prohibited if so desired (see 11.2.1 ). Normally, however, they are permitted. You may, if you wish, turn this button ON to issue a warning that such a record is about to be created. Default action on Return According to the Style Guide for RISC OS typing Return anywhere inside a dialogue box ought to have the same effect as clicking on the default action button (that s the one with the yellow channel border) regardless of which writable icon the caret is in. If that s what you want then select this button. Many users (including the author!) it and prefer the default action to take place only if the caret is in the last writable icon when Return is typed. Hence the default setting, which is OFF. Strip leading spaces causes spaces at the beginning of data fields to be stripped before the record is written to the file. Strip trailing spaces causes spaces at the right of data fields to be stripped. Such spaces are usually the result of pressing the space-bar by accident when entering data quickly and are therefore unwanted. Balance every <n> records forces rebalancing of the current index at regular intervals. The need to balance indexes and the use of this option is explained in Remember place in subfile ensures that, when hopping between subfiles, the first record displayed is the one you were looking at the last time you examined that subfile. With the button deselected the first record in a newly-selected subfile is always displayed. Save marked status saves a list if records which are marked for printing etc, together with the include/exclude status (see Auto-open user menus causes field-linked user-menus to open whenever the caret enters the associated data field, without the user needing to click the menu button. Re-load last report causes the last-saved report from a window to be automatically re-loaded and displayed on opening he database. Query by example has already been described in section in connection with creating reports. If selected it is active for operation for which you would otherwise type a search formula into the Query panel, i.e: all operations (printing, counting, setting/clearing marks) involving the Match window using a global changes batch move/delete subset export csv export interconverting relative and absolute pathnames for Remote objects When any of the above functions is invoked with QBE enabled, a blank record is displayed for the entry of target values. The usual dialogue box also appears but with the writable icon shaded on the Query panel. After entering the values to be matched the default action button on the dialogue box is clicked as usual. If ADJUST is used, so that the window stays open, the next click on the default action button presents the blank record again for new target values to be entered. 14.1.5 Save indexes (Default: Manual) Indexes are always saved whenever you close a database or quit Powerbase and only fear of power cuts or system failure (which includes some kind person switching off your computer!) need make you concerned about saving them yourself. With the Manual setting nothing happens during normal working unless you click Update on the tool-pane. Selecting Automatic Warn only allows you to type in a suitable time interval (default = 10 min) at which Powerbase will either issue a warning to save your indexes (using Update ) or actually save them for you. 14.1.6 Start editing at When a record is displayed the caret is placed, by default, in the first editable field. Editing may be made to start at a different field by choosing Start editing from the Field submenu (see 2.5.4 ) or, alternatively, by placing the caret in the required field and typing Ctrl @ . The tag of the relevant field is stored in this writable icon in the Preferences window and can be saved along with the other preferences. 14.1.7 Application for Impulse data-merg As noted elsewhere ( Powerbase can merge directly into a suitably-formatted Impression document by simply dropping the document icon onto the record window . The dialogue box which then appears contains a writable icon which shows the application with which Powerbase will merge. This name is, by default, Impression , but may be altered by users for their own purposes. An application which will receive data from Powerbase in this way must make use of the Impulse II protocols as defined by Computer Concepts. Even if you are using Style Publisher the name to which these applications respond is still Impression When using Ovation Pro you may, if you wish, alter this entry accordingly but it isn t actually necessary; Powerbase can tell from the type of the document dropped onto the record window whether you are using Impression Ovation Pro 14.1.8 Save choices This section of the window offers two ways of saving the settings three if you are using RISC OS 3.5 or later. See 14.11 for a description of how these options work and the effect of the Load defaults button. The same ideas apply to any window from which choices may be saved and reloaded. CSV options Section explains how to access the CSV options window and what the pop-up menus and option buttons are for. The buttons for saving your choices work in the exactly same way as described above for Preferences, as do Accept Cancel , and Load default . The file is saved as a plain text file called CSVoptions with the same choices of location as described above for Preference Print and Printer setup options These are described at some length in , especially in 3.10.2 . All that needs to be said here is that the Save buttons work like those on the Preferences and CSV options windows except that, when saving choices for an individual database, you are offered a standard Save box and given the opportunity to give your own name to the file and save it wherever you like, the default being in PrintRes . It is anticipated that most users will have a need for several different print options files, differently named, for different ways of reporting from the database. Remember that a file called !PrintOpts saved in PrintRes will be used as a default if no other settings are made. Powerbase own default file is !Powerbase.Resources.!PrintOpts. You are reminded again that the choices in both windows are saved in a print options file. Layout grid options These are more or less self-explanatory and are saved as a data file GridOpts !Powerbase.Resources . No provision is made for saving files for individual databases, but if using RISC OS 4.33 you may save choices for an individual user. Colours of key fields and table-linked fields These may be selected in a window reached by choosing Colours from the Miscellaneous submenu. The following default colours are used: Foreground Background Descriptor key field (inactive) dark blue light grey Descriptor key field (active) light grey Data icon of primary key field (s) black pale yellow Data icon of field linked to validation table dark green white Data icon of mandatory field white Clicking with SELECT on the descriptor and data icons within this window makes either the foreground or background (depending on the setting of the two radio buttons) cycle through the available colours. Clicking with ADJUST cycles through the colours in the opposite direction. The effect of the changes can be seen immediately. Choices are saved as a file called FieldCols and the options are the same as for Preference Defining the function keys Any button on the tool-pane or keypad may have its action duplicated by one of the F1-F11, with or without the use of Shift or Ctrl. Point at the button to which you wish to assign a key and click MENU to display the Keystroke window, which shows the present key-assignment, if any. Use the icons to select the required function key and, if desired, select one of the buttons for Shift and Ctrl. Click on the Assign button and you will now find that the assigned key exactly mimics the action of the button. In three instances Powerbase will automatically assign keys with Shift for you: The shifted version of the key assigned to the Search button will be assigned to the search all subfiles function (see 2.3.1 The shifted version of the key assigned to the record button will be assigned to the Copy record function (see 2.5.3 The shifted version of the key assigned to the Table button will be assigned to the toggle validation on/off function (see and 14.1.4 You may save your key definitions by clicking Save choices and you will find them operative next time you use Powerbase . As with the grid options ( ) there is no provision for saving keystroke de nitions for individual databases but (under RISC OS 4.33) they may be saved for your personal use (see 14.11 The default keystroke s (together with some which duplicate other actions) are listed in Appendix B . If they have been redefined you may restore the defaults by clicking Load d efaults . If you wish to the redefined keystroke s permanently you should restore the defaults as described then Save choices List displays all the key definitions (including those for additional functions) in a window from which the information may be saved as a text file. Function keys may also be used to enter data into record fields or writable icons in dialogue boxes. The actual programming of the keys is done at the command line which is accessed by pressing f12. This takes you temporarily out of the desktop and gives you a prompt at the bottom of the screen. Suppose you want to program f3 to enter the word Powerbase . Type, at the star prompt: Key3 Powerbase and press Return twice to return to the desktop. The string assigned to the key may be entered by typing Ctrl Shift F3 . Any of the keys F1-F9 may be programmed and the string is always entered by typing the key with Ctrl and Shift both held down. Overall control of the database Powerbase database is normally controlled in four ways: mouse-clicks on the tool-pane or keypad keystroke s which mimic the actions of the above choices from the main menu choices from the iconbar menu Each of these can be disabled by deselecting one of the option buttons on the Password window (see ). The main reason why you might want to do this is to provide limited facilities to someone else who is using your database. It doesn t, on the face of it, make a lot of sense to disable both the tool-pane the keystroke-equivalents since no browsing or editing would then appear to be possible. You can, however, place some or all of the tool-pane buttons on the record window itself (see 4.2.13 ) thus allowing you to choose what functions you want to allow access to. You might, for example, wish to provide only the Single-step and Search buttons. Define these as Button field s at the time the record is being designed then disable the tool-pane (or keypad) and keystroke equivalents. Other buttons in the Password window enable you to stop the user printing reports or exporting data as CSV files subset Config files The plain text file !Powerbase.Resources. Config determines certain aspects of the program s operation which will seldom need changing. Changes made to Config take effect only when Powerbase is next loaded. Each attribute is identified by a token word and there is a comment line describing each. It is also possible to place a le in the computer !Boot application, in the sub-directory pointed to by the system variable Choices$Write. Such a le will then be used in preference to the one in !Powerbase.Resources . See 14.11.1 for more details. A database may also have its own Config file, placed inside the database directory, but some settings are only permitted in a master Config file located either in Resources or in !Boot . These are indicated by an asterisk in the following description. Each feature is identified by a token which is followed by either a numeric or string parameter and the order in which entries appear in the file is not important. Lines beginning with are comments. Fields Maximum number of fields per record (default = 100, maximum = 127) Keys Maximum number of subsidiary indexes (default = 10) Tabs Maximum number of validation tables (default = 10) Cols Maximum number of columns in a validation table (default = 20) MaxLibs Maximum number of customisation libraries (see Ch.15) (default=10) CustDir Pathname of recommended directory for customised databases (default= Default , the directory containing Powerbase itself.) Scrolls Maximum number of scrollable lists (default=5) BTime Number of seconds for which start-up banner is displayed (default=2, maximum=5) LeftOpen Left-opening iconbar submenu (default=NO. Most users hate it but it might be preferred if the Powerbase icon is near the right-hand end of the iconbar.) BackGnd Background colour for record window. (default=1, which also allows a background texture to appear if configured under RISC OS 4. Other numbers in the range 0-15 produce non-textured backgrounds in the standard Wimp colours. The colour be set for individual databases but then affects only the background colour of field descriptors not very useful.) CBsize Width and height of Check-box icons (default=48,48 in OS units. Check-box icons need to be slightly larger than their sprites so that a noticeable black border appears when they are ADJUST-selected for printing. The RISC OS 4 sprites are smaller than those used previously and a setting of 44,44 still shows an adequate border. If you want to group Check-boxes so that they actually touch when stacked vertically the height can be reduced still further so that the horizontal borders disappear altogether.) Margins Right and bottom margins of record window (default=16,16 in OS units. The record window is sized in such a way as to accommodate the longest and lowest fields, and the margins set here are additional to that minimum.) ScrollBars Whether or not scroll-bars are present on the record window (default=YES, but small windows which don t need to be scrolled look tidier without them.) ClearSel Clear print selection after creating report (default=NO) AllSpaces Allow primary key to consist of spaces only (default=NO. A rare requirement requested by one particular user!) Upper Force password entry in Access window to uppercase (default=NO) DirOpts Display option for directories opened by buttons on the record window (default is -si, meaning small icons . This may be changed to -li or -fi for large icons and full information respectively. The options -sn, -st, -sd, -ss, to sort by name, type, date and size may also be added.) ExtFiles (default=NEW. Method used for storing Text, Sprite and Draw files which are linked to External fields. You should not change this setting.) PathLen Maximum pathlength for Remote filer objects (default=255; max. prior to RISC OS 4) FontAdj Adjust width of data fields to take account of desktop font (default=YES) Multi Continue to multi-task when printing lists, indexing etc. (default=NO) Markpane Attach pane for marking records for printing etc. to record window. (default=YES). NameLen Maximum filename length (default=10. If you regularly use something like LongFiles , or if you have RISC OS 4 and an E+ formatted disc, you might want to increase this value but beware if you give someone one of your databases containing saved files with names longer than 10 characters!) ButtonAtts Attributes of Directory and Run file buttons when used on record window (see 4.2.11 ). (default=42, the width of the button in pixels. The sprite will be vertically, as well as horizontally, centred on the button unless you add L to the number. If you do so then the name of the attached file or directory will appear on the button underneath the sprite. You will almost certainly need to make the button much wider to accommodate the name.) DialDelay Double-clicking with SELECT on a field containing a phone number whose tag or descriptor contains the string TEL (case insensitive) will attempt to produce dial-tones through the internal speaker. If you hold a telephone handset where it can the tones the number will be dialled. This Config option allows you to specify the delay in second between the double-click and the tones. (Default=5) TimeFirst determines the interpretation of incomplete entries in Time fields (see 4.2.5 ) Default=H, which means first number entered is assumed to be Hours, next is Minutes and last is Seconds. S causes numbers to be interpreted in reverse order. FullTime Either always display as hh:mm:ss (default=YES. Changing to NO allows mm:ss for times under 1 hour.) Output What to do with output destined for the printer Default=1, send directly to the file called printer: . 2 means send first to Wimp$Scrap then copy to printer: The latter may be needed to overcome problems with certain combinations of hardware and versions of !Printers. Messages file This text file in the Resources sub-directory contains all the error and warning messages used by Powerbase including those for Acorn s interactive application. The wording may be altered if you wish. Many error messages contain the string and possibly and . Data items are substituted for these when the message is printed so don t omit them or alter their logical placement within the message. 14.10 Giving a database a title screen You might like the idea of having your database start up by displaying a picture and/or some fancy text as an introduction. You can do this by devising a suitable sprite and saving the sprite file inside the database directory under the name Title . Only the name of the sprite is important; the name of the sprite itself is immaterial and, if the file contains more than one sprite, the first will be used. When the database is opened the sprite will cover the record window and is scaled to fit. The aspect ratio of the sprite is maintained, which means there will usually be strips of grey bordering the sprite, either at the sides or at top and bottom, since it is most unlikely that the record window s aspect ratio will match that of the sprite. If you want to display text it needs to be part of the sprite. The easiest way is to design your title screen in then grab it as a sprite using Paint snapshot facility. As soon as you click on a tool-pane button, or anywhere on the title screen itself, the sprite vanishes. You can bring it back if you really want to by clicking ADJUST on the tool-pane button (not very intuitive but such a trivial feature hardly deserves a special button or menu entry to itself!). If you later decide to dispense with the title screen you should simply delete the Title file. 14.11 Saving and re-loading choices User-choices governing various aspects of Powerbase are saved in the following !PrintOpts Choices speci cally concerned with text output, whether to a window, a text le or to the printer. CSVoptions Choices determining the way data is exported as CSV and related les, and the interpretation of les imported into a database. Preference General choices which might frequently need changing and therefor should be easily accessible. BackupDir The pathname of the directory used by the Backup button, the number of backup generations saved and whether or not backup is automatic on closing a database. FieldCols Colours used to identify key elds, table-validated elds and mandatory elds. Fkeys Keystroke equivalents for the keypad/tool-pane buttons. GridOpts Options for the grid used when laying out the record format. Config Options which only need altering at rare intervals, if at all. For all except the choices are made via dedicated windows and provision for saving the les is present on those windows. 14.11.1 Where are choices les saved? Windows from which choices may be saved have a Save choices button and either two or three radio buttons to select the place where the choices le is saved. In all such cases one radio button is called in Powerbase . If this is selected the choices will be saved in !Powerbase.Resources . In a new copy of Powerbase the Resources directory contains of the eight les listed above not even . The les are present in another directory, !Powerbase. Initial from which they are copied to Resources when you run Powerbase for the rst time. However, each le is copied only if there is no le of the same name already present in Resources . This system is used because users often update Powerbase by dropping a later version on top of an older one. If they have altered the choices les to suit their own requirements the altered les will not be overwritten by the ones from Initial . It goes without saying that you should not alter any of the les in Initial and no means of doing so is provided from within Powerbase . One way of discarding an unwanted choices le is to delete it from Resources . The next time you run Powerbase the missing le will be identi ed and the factory default copied from Initial All relevant windows except those for grid options and keystroke equivalents also have an in database radio button. This allows the choices le to be saved within the database directory (in the database PrintRes directory in the case of print-options les). Such a choices le will be used only by the database to which it applies and will be used by that database in preference to any other such le. Since choices of keystrokes and grid options are unlikely to vary with the database in use, no provision is made for saving database-speci Fkeys GridOpts All the choices windows have another radio button which is only selectable if you are using RISC OS 3.5 or later (it is shaded under RISC OS 3.1) and is of use especially to users of RISC OS 4.33, which supports individual users by providing a log-on screen into which a user name and optional password are entered. If that facility has been enabled there will be a private directory for each user in !Boot.Choices.Users and the system variable Choices$Write will point to the logged-on user s directory. When Powerbase is run it creates a subdirectory inside the user directory and choices les will be saved there if the aforementioend radio button is selected. The radio button itself is labelled for <username> leaving no doubt that you are saving choices for your use alone. If your version of RISC OS doesn t have support for individual users the radio button will be labelled either Single (meaning single user) or Choices depending on how Choices$Write is set by the boot sequence. This is still a useful place to save choices les, although all users of the computer will be using the same les. To view the directory pointed to by Choices$Write, press f12 to access the command line, type: Filer_OpenDir <Choices$Write> and press Return. 14.11.2 Priority of choices If we consider one of the choices les, Preference , for example, you can see from the above that there could be as many as four copies to consider: A database-speci le stored inside the database application itself. A user le in !Boot.Choices.Users.<Username>.Powerbase (or wherever Choices$Write is pointing). Powerbase working default le in !Powerbase.Resources factory default !Powerbase.Initial The last of the four may usually be ignored. The other three are used in order of preference, as listed above, when you open a database, i.e. Powerbase looks rst for a database-speci le and loads it if present. If no such le is found it looks for a user le. Failing that, the le from Resources (whose presence is guaranteed) will be loaded. When the relevant choices windows are examined the window title indicates the source of the le currently being used. 14.11.3 What does the Load default button do? Simply clicking with SELECT or ADJUST reloads whichever le was originally loaded when the database was opened, as determined by the priority order described above. Thus, if there is a database-speci le then that will be loaded, the next choice is a user le and the nal choice the one from Resources There might be occasions when you wish to override this order. SHIFT-clicking will preferentially load your user le (if any), CTRL-click loads the le from Resources . You can even load the le from Initial by ALT-clicking. 14.11.4 Preserving your own choices when upgrading It has already been mentioned that copying a newer Powerbase onto an older one will not overwrite les saved in !Powerbase.Resources. For RISC OS 3.1 users this is the only place (other than in an individual database) where such choices may be preserved. Users of RISC OS 3.5 or later are reommended not to use this method but to save the les to Choices$Write as described in 14.11.1 They will then remain intact even if Powerbase is accidentally deleted. L - 7 0(% - 0x\ - d -P@ C - 7 08J - Ch 15 Customising Powerbase This is the most technical chapter in the manual and can safely be ignored by anyone who wishes to use only the built-in features of Powerbase as described in previous chapters. If you have rather special requirements and if you have some experience of BASIC programming then read on! Powerbase has many powerful and useful features but, as with any software, some users will have special requirements which aren t catered for. This is why many organisations produce their software in-house as the only way of ensuring that their specific needs are met. I first came across this situation in the F.E. college for which Powerbase was originally written and in which it was (and still is) used to maintain student records. I was asked if the program could be enabled to print students personal timetables and, since Powerbase was used by so many other people for diverse purposes, was unwilling to include code for this very specialised function in the main program. The solution developed at that time was to provide the special feature in a subsidiary program file which was loaded as a library and to give Powerbase hooks to enable it to recognise the library and create the controls which gave the user access to the extra facilities. This method of adding features to Powerbase has never been fully documented until now but has been progressively refined to the point where it can be offered to users in general. A demonstration The directory Customise is provided for you to try out database customisation easily and painlessly. It doesn t do anything which is actually useful but it does show how non-standard features can be added to either an individual database or even to all the databases you use. Take a Powerbase database, any database as long as it makes use of the tool-pane, open its directory with Shift-double-click and copy Customise into it. Now run Powerbase and open the database in the usual way. Straight away you ll notice something non-standard; a little window which appears for a second to tell you that the database is open. A corresponding message is displayed when you close the database. (You were warned not to expect the demo to do anything really useful!) At the bottom of the tool-pane you will see three buttons which aren t normally present. One is a blue, square button resembling a house window. Next to it is a pop-up menu button, which you will have often seen elsewhere. Beneath these is an action button bearing the legend Click Me! Who could resist such an invitation? What it does is display a plain-text copy of this documentation. Click on the window button and (surprise, surprise) a window opens. You won t have seen this window before because it forms no part of Powerbase even though it has been invoked from Powerbase s tool-pane. Clicking the three buttons on the new window causes incrementing values to appear in the icons above them. (Decrementing values if you click with ADJUST instead of SELECT.) Click the menu button and you ll see choices which will clear the numbers in the icons on the special window. There s an item Print which produces a report showing the values of the three counters and allows it to be saved as a text file in the usual way. A submenu contains a single entry which, when chosen, calls up a validation table you didn t know you had. How is it done? If you look inside Customise you will see several objects. The Templates file producing the non-standard window is there, so is the validation table called up from the submenu. The text-file is the one displayed by the Click me! button. The thing which does most of the work, however, is the BASIC file called , all of whose FN/PROCs become accessible, when required, as if they were part of the !RunImage itself. It s now time to load Demo into your favourite editor (Zap or StrongEd but not Edit, I hope!) and have a good look at it. Note first of all the frequent occurrence of the string in the names of variables, functions and procedures. When assigning names to FN/PROCs and to variables specific to the library it is important to avoid clashes with names which are used within Powerbase itself and also with names in other libraries. You may have as many databases as you like, each with its own customisation library tailored to add specific features to that database, but no two libraries may contain identically named functions or procedures. Having identically-named variables is likely to be less serious but should still be avoided. Achieving the necessary uniqueness in naming isn t difficult if you start out with the Demo library and do the following: Choose a suitable name for the BASIC library le and rename Demo to that name. The choice of name isn t critical but do keep it to 10 or fewer characters not everyone can use long lenames. Where a library provides facilities for one database exclusively it is good practice to use the database name (minus the initial ) for the library. Globally replace all occurrences in the le of the string with the same name as used in (a). By we mean an exact match, including identical use of upper and lowercase letters. This will ensure that the following functions in particular are re-named in a fashion which Powerbase can recognise*: FN_Demo_setup FN_Demo_button FN_Demo_select FN_Demo_click FN_Demo_press FN_Demo_drop FN_Demo_function These seven functions are called from Powerbase and must not have their names changed once you have made the changes described in (b) above. If you have made those changes, try out your database again. All the non-standard features should work exactly as before. If the database and Powerbase are in the same directory you will notice no difference whatsoever. If, however, they are in different directories you will, on opening the database, see a message recommending you to move it into the directory containing Powerbase . The reason for this will be explained later. Note that although you should not use the same name for different libraries (you re likely to get program crashes and strange error messages if you do) it is possible to use the library in more than one database, which is useful where a library provides general facilities not geared to one speci c database. What happens when Powerbase is run? All the objects in a designated directory are examined to see whether they are databases containing a Customise directory. If any such are found, the pathnames of the BASIC libraries they contain are placed in a string array which is then used with the OVERLAY command. When any function is called which is not found in the !RunImage each library is loaded in turn until the required function is found and executed. Since all the libraries use the same area of memory, only one being paged in at any given time, this system makes economical use of RAM. The memory area used must be large enough to accommodate the biggest library, which is why it is advisable to have all your customised databases in a single directory (the designated directory referred to at the beginning of the paragraph) so that the maximum size of library can be found before any database is opened. This directory is specified in !Powerbase.Resources.Config by means of the entry CustDir (see ). As supplied this entry has the value Default , which means the same directory as that which contains Powerbase itself, but you may replace Default with the pathname of any directory you wish. Another relevant Config entry is MaxLibs ; the maximum number of libraries which can be handled. The default setting is 10 but you may increase this if necessary. You can obtain a list of the libraries recognised by choosing Utilities=>Libraries from the iconbar menu. Double-clicking on a line of the displayed list will open the relevant Customise directory. What happens when a database is opened? If the database has a Customise directory, Powerbase looks at the list of libraries which it knows about to see whether the database s library is one of them. If not the new library is added to the list, allowing you to use the customised features of databases outside the directory specified by CustDir but, if the new library is larger than any of the known ones, some memory will be wasted. For this reason you will see a message advising you to move the database into the same directory as the others. The message only appears the first time you open the database during a given session. Once the correct library has been identified Powerbase calls the function FN_<name>_setup (where <name> is either the database name or ). This, the first function in the library, is only called once while the database remains open and does the following: Create buttons on the tool-pane, keypad or record window. These may be the window and menu buttons already seen in the demo, action buttons like Click me! (which may be called anything and can be made to do just about anything), square option switches and groups of radio buttons. DIMension global arrays for the library s especial use. Declare important variables which the library might require. Load validation tables which haven t been loaded by the opening of the database. Anything else which is an essential, one-off , task for the functioning of the library. Creating buttons As can be seen from DEF FN_<name>_setup , the special buttons are created via a function FNadd_button . This function is part of Powerbase itself and takes seven parameters, returning as its result the icon-number of the newly-created button: button%=FNadd_button(wi%,S$,type$,grey%,x%,y%,W%) The meaning of the parameters is as follows: The handle of the window on which the button is to be created. This must be either mainW% (for the record window) or keypadW% (even if the tool-pane, rather than the keypad, is being used). The text which is to appear on the button (if an action button) or adjoining it (if a radio or option button). It may also be used in menu and window buttons on the record screen (but not on the keypad/tool-pane) to supply a descriptor for the button. type$ This may be window option radio . Any other value is understood as an action button. grey% A boolean variable which determines whether the button is shaded (TRUE) or not (FALSE) on first appearance. x%,y% Co-ordinates specifying the position of a button on the record window. They refer to the lower left corner of the button icon, the origin being the upper left corner of the window. y%, therefore, is always negative. Buttons on the keypad/tool-pane are positioned by Powerbase and any values supplied by the user are ignored. The width of the button icon in OS units. It does not apply to window or menu buttons on the keypad/tool-pane (these are always 44 OS units square) but is relevant for all other types. For action buttons it is the width of the button itself, for all others it is the width of the button plus accompanying text. A value of W%=0 causes Powerbase to set the button width for you. You should normally do this for buttons on the keypad/tool-pane. These should be 144 OS-units wide and you should adjust the text so that it can be displayed within this width using the current desktop font. Buttons on the record window may be wider than this and, if W%=0, Powerbase will increase the width to accommodate the text. You can override this action by supplying your own value to make the button either wider than the automatic setting or narrower than the normal minimum of 144 OS units. This is useful if you wish to standardise the width of a group of record window buttons. What goes inside the IF...THEN structure? It will be seen that most of the setting-up in FN_<name>_setup occurs in the body of the IF...THEN structure, i.e. in lines 22-31 incl. Anything which must/should be done only once during a given Powerbase session with a particular database goes in here. Obvious examples are the DIMensioning of a global arrays and the reading of window definitions from a Templates file. Don t put calls to FNadd_button in here because the custom buttons are removed from the tool-pane when the database is closed and icons (including buttons) on the record window are created from scratch on the empty window on opening. Detecting events Section listed seven functions within a customisation library which are called from the main Powerbase code. FN_<name>_setup has already been described. The others are called under the following circumstances: FN_<name>_button(wi%,ic%,b%) This is called whenever the user clicks on one of the custom buttons. contains the handle of the window which owns the button. This will either be mainW% (button on record window) or keypadW% (button on tool-pane or keypad). gives the state of the mouse buttons and the user can test the bits to see which button was pressed and whether double-clicked or not. is the icon number of the button. and all such numbers will be known to the programmer because s/he will have set them up using FNadd_button. There will usually be at least one CASE statement to determine which button was pressed and take the appropriate action, such as opening a menu or window. FN_<name>select(menu%) Called when the user chooses from a menu which is not recognised by Powerbase and must therefore be one created by the customisation library. menu% is the handle of the menu and the item chosen is present, in textual form, in choice$(1) . If the choice was from a 1st-level submenu the submenu choice will be in choice$(2) and so on down to choice$(4) FN_<name>_click(wi%,ic%,b%) Called when a button is clicked in a window which Powerbase doesn t recognise and which must therefore be a window created within the customisation library. FN_<name>_press(wi%,ic%) Called when a keypress occurs and the window having the input focus is not known to Powerbase . The key code is in key_pressed% FN_<name>_drop(wi%,ic%) Called when a file is dropped on a window not known to Powerbase FN_<name>_function(func%) Called when certain events apart from the above occur in Powerbase . These events are only reported if the associated flag special%(n) has been previously set to by the customisation library. There are eleven of these flags and not all have been assigned. Those which have been assigned are described below. There is no reason why the number shouldn t be increased in future if users make use of customisation and report their requirements to Powerbase Support. Database opening has been completed. A record has just been read into the record window. The array Rf%() holds pointers to the icon text-buffers for the fields. A copy of the record is held in field$(). Scrollable lists and External fields have been read and all keys are now present in key$(). A record has been read into array F$(). This is the array used when printing reports and for some other operations, so that the displayed record is not affected. Only normal text and numeric fields are read: where field n is a scrollable list, external or button, F$(n)= The displayed record has been written, including the writing of Scrollable lists and Externals and the insertion of keys into indexes. The record held in F$() has been written back to Database. tree-walk or file-scan of the current key has begun. The keys will be accessed in order until the end of the subfile is reached. If more than one subfile is involved the subfiles are or scanned in sequence. The aforementioned file-scan has finished. A record has been matched to a search formula in the course of a file-scan Database is about to close. The global variable Postpone% can be set to TRUE if you want to put off closing. * Throughout this chapter Corpus Medium font is used to represent BASIC variables and functions. Appendix A Powerbase as an Impulse server The following is a description of the Impulse methods understood by Powerbase , given in the standard format recommended by Computer Concepts. Users wishing to write their own Impulse tasks to communicate with Powerbase will need this information. They will also need details of the SWIs to which Impulse will respond, and this information is obtainable from Computer Concepts. {methods: GetPathname Selection <string> ParseQuery <string> GetField <tag> GetRecord PutRecord ExpandCode <string> GetExpanded <string> NextMatch Description: GetPathname specifies an object i.e. a Powerbase database. If Powerbase has the required database open it replies with the full pathname of the object, otherwise it returns an error message. Example of use: :Powerbase !Elements GetPathname (returns pathname of !Elements R6 points to pathname when calling task is decoding reply. Selection <string> tells Powerbase about a field, or group of fields, in which the caller is interested. The fields are specified as a list of , separated by any suitable character (e.g. ). The same separator must appear at the very end of the tag-list. Powerbase replies with the maximum data-length of the combined fields in the selection. Example of use: :Powerbase Selection NAME/SYM/Z/ R6 points to a string which gives data-length. ParseQuery <string> informs Powerbase of the criteria to be used in selecting records to transmit to the caller. The parameter is a standard Powerbase search formula and Powerbase replies with the title which would normally appear at the head of one of its printed lists. Example of use: :Powerbase ParseQuery GP=T R6 points to returned title. GetField <tag> requests from Powerbase the data in the specified field of the next record which matches the preceding ParseQuery command. When Powerbase replies to the GetField command the calling task should respond with an Impulse_ FetchData, specifying the address and length of the buffer to be used, and then wait for an Impulse _Receive event (reason code &204) before reading the buffer. Example of use: :Powerbase GetField NAME GetRecord [<key>] (The square brackets signify that the key is optional: they are not part of the command.) Sent without a key it requests from Powerbase the next record which matches the preceding ParseQuery command. If the primary key of a record in the database is appended then Powerbase will return the record corresponding to that key. The key must be separated from the GetRecord by a space. The calling task must follow the procedure described above for GetField. Powerbase will transmit the fields specified in the Selection command, using the same separator as was used in that command. Receipt of data may be followed by another GetRecord command, returning the next matching record and so on, a zero-length response signifying that there are no more matching records. If using to request specific records you should end by sending *** as a key to tell Powerbase that the dialogue is finished. This command and the one below operate only on Subfile 0 of the Powerbase database. Example of use: :Powerbase GetRecord ACTI PutRecord informs Powerbase that the caller wishes to write a record to the current Powerbase database. It should be sent as message type &200. A Selection command should have been previously sent to tell Powerbase what fields to expect and what separator is to be used. Powerbase will then reply with a GetRecord command (reason code &201) which the caller should acknowledge with message type &202, specifying the maximum length of the data which will be sent. Wait for event &203 which is a request by Powerbase for the caller to transmit the data. The caller should then do so with Impulse _TransmitData, specifying the buffer address and the length of data being sent. This may be immediately followed by another PutRecord. ExpandCode <string> requests the expanded form of a code used in a field linked to a validation table . The parameter string consists of the code itself, then a space, then the name of the validation table with the number of the linked column appended. Example of use: :Powerbase ExpandCode T Group0 GetExpanded <string> combines the functions of GetField and ExpandCode, i.e. it requests the expanded form of the contents of a specified field. The parameter is the tag of the field. It is not necessary to supply the name of the validation table or number of the linked column since Powerbase can determine these from the entry in the file linking the field to the table. By default the second column of the table (i.e. column 1) is used to supply the expanded string, but this may be overridden by appending the relevant column number preceded by a comma as in the second example. Remember that the first column is numbered 0. Examples of use: :Powerbase GetExpanded GP :Powerbase GetExpanded GP,2 NextMatch is designed to remove the restriction whereby only the data from a single record can be merged into a given document. All it does is tell Powerbase to access the next record matching the search formula in the Merge window and interpret subsequent Merge commands by taking data from that record. Appendix B Keystroke equivalents Only the function keys in the following list are re-assignable (see ); the Ctrl-letter keys are fixed. Users are also reminded that re-definition of F8, F9 and F10 will cause the Shifted variants to be assigned automatically (see Next record in subfile Fast forward (default: 10 records) Shift Final record in subfile Previous record in subfile Fast rewind (default: 10 records) Shift First record in subfile Update Select next subfile Select previous subfile Select next key Shift Select previous key Shift Shift record forward Shift record backward Delete record Add new record Copy displayed record Shift Search for key in current subfile Search for key in all subfiles Shift filter List validation table for field with caret Turn validation ON/OFF Shift Play (rapid scan of records) Stop (cancel above) Shift Show keypad Print report Print Print displayed record Shift Print Display Print Options window Print Set field where editing starts Select all fields for printing Balance current index & display brief results As above, but display complete index tree Shift B Copy field contents to clipboard Enter current date Edit record template Display list of fields, or of selected fields Open Changes dialogue box for current field Display Indexes directory Open Index dialogue box for current field Show structure of currently-selected key Open dialogue box for current field Toggle mark-pane ON/OFF Open Numeric field calculations window Retrieve last search formula Display PrintJobs directory Display ValTables directory Display PrintRes directory Swap case of character to right of caret Enter current time Blank field Paste field contents at caret Restore keypad & record windows to initial state Export CSV file Display UserFuncs directory Clear field selection Add new row to scrollable list (when in last cell) Shift Return As above, but from any cell & without scrolling Insert Delete blank row from scrollable list Shift Insert Delete row with caret from scrollable list Shift Insert Index !BMARGIN !BODYFONT !CASE !CHANGE !CLEAR !COMMENT <string> !COPIES !CSV !DATA !DATE !DELETE !DESTINATION !DUMP !ENDLOOP !EXCLUDE !EXPAND !FILTER !FILTEROPEN !FIND !FIRST !FIRSTPAGE !FONTSIZE !FORMAT !HEADER, !FOOTER !HEADERFONT !HEADINGS !IMPRESSION !INCLUDE !INDEX !INSERT !KEY !LABEL !LAST !LINESPACE !LMARGIN !LOOP !MAILMERGE !MESSAGE !MOVE !NEXT !OBEY !ORIENTATION !PAGE !PAUSE !PMARGINS !PREVIOUS !PRINT !PRINTCOLUMNS !PRINTOPTS !QUERY !RMARGIN !SAVE !SCRIPT !SELECTION !SHRINK !SORT !SPACER !STARTAT !SUBFILES !TABLE !TEXTWIDTH !TITLE !TMARGIN !UPPER !WAIT @ in search formula to search all fields @+Ctrl to set start field for editing Absolute pathnames for Remote fields Absolute pathnames, conversion to Relative Access-control list Accumulating records in one subfile 11, 15 Adding new records Add record button 11-12 Adjusting record format Allowed list in validation table 45-48 Altering record format Altering existing records Analysing an index Analysing date field by months AND, use of in search formulae Application shell, creating Arbitrary selection of records, printing Arrow keys, action of Ascending sort Auto-balancing indexes Auto-opening user menus Average of column for numeric field Background colour of record window Balancing indexes 68, 82 Banner, start-up, time displayed for Binary Large OBjects (BLOBs) Blanking record on deletion Blue descriptor, meaning of Bookmarks, using Bounding box of field, altering Browsing Button fields 39, 84 Calculated fields 50-52, 54 Calculation formulae Calculations during global changes Calculations involving dates Calculations involving times Calculations on a column of a report Calculations, making retrospective Calling up records from the report window Case button on Query panel 22, 31 Case-sensitive queries Change database size Changing key Changing many records at once Changing subfiles Check-boxes 20, 32, 35-36, 54, 61 Check-boxes, re-sizing Clipboard, using 13, 94 Closing a database Colours, use in reports Colours of key fields Colours window Columns in validation table, maximum Column headings in reports Columns, printing Scrollable lists in Compacting sequence numbers Comparators in search formulae 22, 26 Comparison by numeric value, forcing Composite fields 50-52 Computed fields 38, 50, 52, 81 Concatenation of fields Conditional validation Config files Contiguous range of fields, selecting Copy and paste (clipboard) Copy to clipboard Copying an entire record Copying fields Count (number of values in column) Cross-referencing records CSV file, making database from CSV files 16, 34, 48, 57-60, 63, 68, 71, 79, 84 CSV options 57-59, 61, 83 CSVoptions file Customised databases, directory for Data length 33, 66 Data Merge window Data overflow (CSV import) Data underflow (CSV import) Data merging 63, 82 Database, constructing Database file 8, 16, 36, 55, 66, 81 Database size, setting and changing 41, 68 Date-related fields 29, 31, 34, 38-39 Default action on Return Default database, building 41, 32 Default print options file Default query file Default selection file Defining the function keys Delete button Deleting records 12, 15, 93 Descending sort 18-19 Descriptor Descriptor, altering and re-positioning Descriptor, changing colours of Decsriptor, meaning of colours 8, 55 Descriptor, in CSV header record 57, 59 Descriptor, in report headings 19-20, 75 Descriptor, omitting Descriptor, positioning in record design Design mode Dial-tones, generating Dial-tones, delay before sounding Directory for customised databases Directory button, displaying name with 40, 85 Disabling features from password window Displaying linked table data Doubled comparator, effect of Draw files 16, 36 Duplicated primary keys, listing Duplicated primary keys, forbidding Duplicated primary keys, warning of 71, 81 Editable fields, list of types Editing mode Editing scrollable lists Emailing Powerbase Support Empty database, building Ends button Evaluate button, calculations using Exact match in validation table Exit button Expanding validation table entries 29, 49 Expanding headers Export Export subset Export the linked file External External fields 13, 16, 36, 60-61, 81 External fields, list of types Fast forward interval Fast forward/rewind button Field Field, classes of Field creation Field definition window 32-34 Field menu button Field, selection for printing Field to be substituted on labels Field-concatenation in reports Field-separators in reports Fields, adding to record format 66, 67 Fields, maximum number Fields, menu of selected Fields, printable 20, 21 Filenames in script commands Filter, use of 10-11, 94 Fixed data on labels Flashing primary key field, meaning of Following moved record to new subfile Font, desktop, allowing for Footers in reports 29, 54 Form file Frames in record design Function keys 9, 71, 84 Functioning database from CSV file General menu button Global changes 14-15 Global clipboard, use of Grid for record design Header 29, 59, 75 Header record in CSV file 57, 59, 61 Headers/footers in reports Headings Help application (Acorn) Help window Helpreader text Hidden fields Hiding sensitive data Horizontal format (reports) 18, 19, 29, 74-75 Hyphen, use of in search formulae I.D.s and passwords Iconbar menu 71, 84 Importing data from CSV files Importing data from plain text files Impression, merging data with 63-65 Impulse commands 63, 65, 82, 92-93 Inclusive search Increment button Index 8-10, 44, 55 Index, analysing Index, balancing Index, named in report header Indexes, saving 12, 56, 82 Indexes directory 32, 55 Indexing a field 55, 76 Input from script files Inserting merge commands Internet (field type) Keeping descriptor with data during design Key, changing to another Key, deciding structure of 41-44 Key field 8, 10, 41, 44 Key fields, colouring of Key, searching for record by Key structure 10, 41-43, 55, 66 Key Structure window Key-change button Keypad 9, 39, 71, , 83, 94 Keys, distribution in index tree Keys, repeated primary Keys, maximum number Keystroke equivalents Keystroke equivalents, defning 83-84 Keystroke equivalents, list of 93-94 Keystroke menu Label format Last altered Launch new copy Line terminator Line-spacing in reports Linking files to the fields Linking tables to fields List of available comparators Load defaults Loading Powerbase Loading validation tables Locking fields against alteration Log changes Log file Logging, types of events reported Looping in script files 76-77 Logo fields 36, 38-39 Mail-Merging 63, 65 Main menu 71, 84 Manager access rights Manager password Mandatory fields Mandatory fields (see 4.2.14). Margins Marking records Markpane Match window 21, 26, 39, 73 Matches found, number of Matches in the subfile Maximum value allowed in numeric field Maximum value in column of report Maximum number of customisation libs. Menu buttons Menus subdirectory 32, 40 Merge commands, inserting in document Merging databases Messages file Minimum value allowed in numeric field Minimum value in column of report Month More than one field in a key More than one subfile Move record button Move records Multi-tasking within Powerbase Multi-criterion searches Multiple subfiles, use of Naming subfiles New record format NoCanDo file, purpose of NOT, use of in search formulae Number of lines on the label Numeric 29, 34 Numeric field 15, 43, 45, 50-51, 54 Numeric min Numeric value of the field button Only if targetted in query Opening a database Options, CSV OR, use of in search formulae Order of records in database Order of selection of fields Output destination 18, 27 Ovation , merging data with Ovation Pro , merging data with 63-65 Page length in reports Page numbers in reports Part sheet of labels, using up Password 7, 66, 70-71 Password protection Password window Paste from clipboard PathLen Pathnames: see Absolute and Relative Pathnames for Remote fields 37-38 Pathnames, interconverting Personal passwords and I.D.s Picture fields 16, 36 Play button Pop-up menu Powerbase Support, contacting Preferences 47, 56, 68, 71, 80-81 Preferences window 34-35 Primary key Primary key, altering Primary key, colour of data field Primary key, defining Primary key, examples of 41-43 Primary key, inclusion in CSV file Primary key index PrimaryKey file 41, 55 Primary key, printing on address labels Primary key, repeated, forbidding Print button Printable fields, list of 20-21 Print formats Print Options 18, 28, 31, 49, 57, 59, 74 Print options window, opening Print options files Print Resources Print selections Printer destination Printer setup options Printing, selecting fields for Printing speed PrintJobs directory 18-19, 32, 38, 48, 58, 60, 74, 81 PrintRes directory 21, 25, 30, 32, 73-74, 83 UserFuncs directory 32, 52 Query by example 21, 25, 82 Query by example, limitations of Query files Query method (QSF, QBE), changing Query panel 22, 26-27, 31, 58, 73, 79 Querying scrollable lists Quit button Quitting Powerbase Quotation marks in CSV files RAM disc (footnote) Re-loading last saved report Re-ordering fields during record design Read password Read/write password Rebuilding a database Recalculating existing records Recalculating on opening Record 8, 59 Record edits as CSV file Record layout Record number 8, 38 Record window 8, 10-11, 16, 33-34, 36, 39, 44, 48, 60-61, 64, 82 Record window. Record-terminators Red descriptor, meaning of Relative pathnames for Remote fields Relative pathnames, conversion to Absolute Reloading saved reports Remember place in subfile Remote fields 16, 36 Removing filed linked to External fields Removing field from record layout Renaming a database Renumbering fields during record design Replace on entry 46-47 Report printing 18, 71 Requesting input to script files Restore will put a sorted report Retaining subfile structure (new prim.key) Right-justification, of numeric fields Right-justification, of numbers in keys Rotate buttons Run file button, displaying name with 40, 85 Same tag, where permitted Save box 18-19, 32, 48 Save indexes Saving marked record status Script file, automatic execution of Script file, linking to Run file button Script file, loops in 76-77 Script file, requesting user input to Script files 65, 73 Script language Scrollable list Scrollable list, file dropped on 17, 58 Scrollable list, saving as CSV file Scrollable list, saving as text file Scrollable lists 20, 35 Scrollable lists and CSV files Scroll-bars, removing from record window Scrollable lists, maximum number Search button Search element 22, 26 Search formula 21-22 Search formula, exporting subsets using Search formula, from Help window 26-27 Search formula, construction of 21-25 Search formula in CSVexport Search formula in Filter window Search formula in Global changes Search formula in mail-merging Search formula in report header Search formula in Script commands 73-74, 76 Search formula, saving as Query Search window 10-11 Searching 10-11, 55 Searching all six subfiles Searching by filter Selection, clearing after use Selections, fields Selection files Self-calculating fields Sep and Row end Separator, of fields in CSV files 57, 59 Separators in date and time fields Sequence number fields 38, 59 Sequence numbers, compacting Setting passwords Shifting records betweeen subfiles Shrink line button Shrink list button 18, 29 Single line format for scrollable lists Single-step button Sort, ascending and descending 18, 19 Sorting a report 18, 19 Sorting a validation table Sorting with a subsidiary index Spacer in reports 19-20, 29 Sprite files 16, 36 Stamp fields Stamp fields, list of types Standard deviation for column of numbers Start editing at (field) Start editing at Stop button Strip leading spaces Strip spaces Strip trailing spaces Subfile le, disabling le, enabling le, naming Subfile number in a report Subfile-change button Subfiles 10-11, 51, 66-67, 71, 94 les, rotation of Subset databases Subsidiary indexes 10, 31 Subsidiary key Subsidiary keys Sum of numbers in column of report Swapping fields during record design Table, validation, creating 46-47 Table button Table format (reports) 20, 29, 75 Tabs Maximum number Tag, altering and re-positioning Tag, importance of 33, 34 Tag, importance of when reformatting Tag, in CSV header record 57, 59 Tag, in Key definition window Tag, in report headings 19-20, 75 Tag, in search formulae 22-23 Tag, insertion into query using Ctrl key Tag list in search formula 22-23 Tag, selection using Help window Tag, use in name of saved files 13, 62 Tag, when it may be omitted Tag, when it may be duplicated Target list in search formulae Terminator 57, 59 Text fields Text block fields 16, 20, 29, 33, 36, 60 Text button Text file, as output destination Text files 16, 36 Text width 20, 29 Three-state check-boxes 21, 35 Time entry, how interpreted Time fields 34-35, 38, 51-52 Title (optional) on reports Title-bar, information displayed in TooBig file, purpose of Tool-pane 9, 39, 46, 48, 55, 71, 80 Tool-pane or keypad? TSV files Two-state check-boxes Undoing changes 12, 15, 48 Unrestricted (field-type) Update button 12, 46, 48, 56, 82, 93 Upper case 29, 34, 85 User function User functions Using a template Using CSV files to modify existing records Using more than one subfile UsrSprites file Validation 45-48 Validation, conditional Validation, turning on and off 12, 45, 81 Validation string of field icons Validation table 11-13, 45 Validation table, colour of field linked to Validation table, creating Validation table, displaying Validation table, expanding report headers Validation table, exporting as CSV file Validation table, including data in reports Validation table, linking to field Validation table, loading Validation table, modifying Validation table, saving Validation table, sorting Validation tables and Calculated fields ValStrings ValTables directory 32, 48, 94 Vertical format (reports) 20, 29, 74 Warn of duplicates Warn of external deletion Website for Powerbase Support White space , removal from report 18, 28, 29 Wild-card characters, defining Wild cards in search and replace Wild cards in search formulae Window destination Wiping a scrollable list Wrap-around Year (field-type) Yellow background to field, meaning of Yes/No/Maybe field type tion t eP, 7 eXy 7 e87 7 e`N 7 e0B 7 1_Plain ABC45XYZ 57XYZ LBING ytown EETSYM5 bloggs BRITCERCAR COMPARATOR CONFUSER DAT1-DAT2 david d-mm-yy irOpts EasyV email FastSp dsData GALBERT ydn's HAYDSYM HERRALBE Impul ystroke s MAHLER SONEAR MOZAPIACON23 MyPhotos OBjects outpu ICLIB tJobs ROTOACTINIUM tmost RINER SMITJ kPlug subdi rget1 rflow idgets index contents * DDF: Document Description Format; the means by which styles and effects are encoded within an Impression document. ArcDraw screen wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww @DDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDD wwwwwwwwwwwwwwwww @DDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDD wwwwwwwwwwwwwwwww'q qwwwww qwwwww qwwwww qwwwww qwwwww qwwwww qwwwww qwwwww qwwwww qwwwww qwwwww qwwwww qwwwww qwwwww @DDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDD qwwww qwwq'07 @DDDDDDDDDDDDDDDD! DDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDD +""""""""" +""""""""" +w"ww'rr"' +""""""""" +'w'rww''" +""""""""" +w'r"w'rw' +""""""""" +""""""""" @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD ww"ww q""""" '33333r wwwwwww q""""" qBB"$$ """""""""" qBB"$$ """""""""" qBB"$$ r'rww"''r" qBB"$$ """""""""" qBB"$$ rrw"wwwr"" qBB"$$ """""""""" qBB"$$ rw"'rw"ww" qBB"$$ """""""""" qBB"$$ """""""""" qBB"$$ q""""" qwwwww @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD wwwww qwwwwwwwwwww @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD ww73sww qww33ww pwwwp wwpwpp qppppw wwwwwww qwwwwwww qwwwwww wwwwwww @DDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDD twG!Wwg!' s$'!G A&'1& BUg!7 1''Qwww 1g3d!7 1''Q633 A&2w"w#t#' vws&6vG!& @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD 1'ru&'ruw r7"'r @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD !ww#' r"WwW A'!Tu !7s6C 1&'!7!7 d"s'r&b%' V"w"w#t aw7g!vW!& swd"GvG @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD a%WwW 1ww7Aww s%!v"w#t a&b7d"s'a 1vw&!GvG !vW6aw7gAg @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD vwwWr"'1 1!'a# vg2ww vww$r"'a uww7!' e33#!' b"&Ag"vW BDDDDDDDDD !TUUUUUUUUU qwwwwwwwwwwwwwwwww wwwwwwwwwwwwwwwww q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" 'q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" r""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" r""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" r""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" qwwwwwwwwwwwwwwwww wwwwwwwwwwwwwwwww qwwwwwwwwwwwwwwwww wwwwwwwwwwwwwwwww q""""""""""""""""r '"""""""""""""""" r""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" 'q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" r""""""""""""""""r '"""""""""""""""" 'q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" 'q""""""""""""""""r '"""""""""""""""" 'q""""""""""""""""r '"""""""""""""""" q""""""""""""""""r '"""""""""""""""" qwwwwwwwwwwwwwwwww wwwwwwwwwwwwwwwww wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww ArcDraw screen wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww !""""""2u """""" " """" " """""" """""""S !""""""2u !!!"" " ! "" !""rwwg !!"!"" !!!"" " ! "" !""" " !!"!"" """""""S !"""" """"! "!"""!!" """""" "r62t !"""" """"! "!"""!!" """""" """""""S """"""" "! """ !!!r&!R7!B$!" ""3s34" C$" "! """ !!! !""!! """""2u """""""S !!!" " """"""" "r&"B7Aww$W t#tw72gwsvw&Pww vw&Bww# """"""" """"" """""""S !""""!"" """!"" c's&R7f2v$u2W!e2w4s7BWt$R'BG"Gs Q&!T" """!"" "UUUU%"S " ""!""!"" "!" !""! !!"!"" rwww t#"GtBg s4G!s uC"B'B7!"t "!" !""! !!"!"" "!"" !"""" "%""" A4"BC !""5S!"2u !""""" ""! ""rFD "gsSF6Gbwww47 s$"t3ev'"vg vwwG!""""" ""! """"" "" """" "%""" 0%"2u """""" !"""""! """ !!!"r& v""gbe5W7aGDD37"s "ttFT'"BeGvDD4!"""""! """ !!!"!! """"""!"""""" A$%"!C """""!""!""! "!!!! """""!""" 2Gbw$v&A'"S27"s%!ff!B'A !ft"2%"!!!! """""!""" """"""""1u """""""S """"!"! "!!"r r7c'Ag"v "g3g27"sGSGu3t7BG2Gr6s&"T "!!"" """2u """""""S """""!"""!" "!!"" ""b%"!!1vg sw&26"ctw%bwGw uw%2wg" "!!"" """!"!! """""1u """""""S "!!"% ""!"!!! !!!"! !!""!!"" """"! ""!"!"! "!!"!"" !!""!!"" """"! ""!"!1u """""""S """"%!!C !""""""2u "!!"!! !""""! !!!""""" "!!"!! !""""! !!!""""" """""""S 1UUUU%""C 23333333u 33333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333u 3333333S 23333333C 'TUUUUUUUuBUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUuBUUUUUUUU'DDDDDDDDD wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww qwwwwwwwwwwwwwwwwww! DDDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDD$! @DDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDD !qwwwwwwwwwwwwwwwwww DDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDD qwwww wwwwww wwwwww wwwwww wwwwww """""""""""" qwwq'07 +""""""""""" wwwwww """""""""""" +""""""""""" wwwwww "'w"ww'rr"w" +rr'rww"''r' wwwwww """""""""""" +""""""""""" wwwwww ""'w'rww''w" +"rrw"wwwrr' wwwwww """""""""""" +""""""""""" wwwwww "'w'r"w'rw'" +rrw"'rw"ww" wwwwww """""""""""" +""""""""""" wwwwww "ww''r'w'"w" +rwwr"wrw"r' wwwwww """""""""""" +""""""""""" wwwwww DDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDD !@DDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDD! @DDDDDDDDDDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDD qww33ww ww73sww 1'ru&'ruw wwwww r7"'r wwwww pwwwp wwpwpp qppppw qwwwwwww wwwwwwwwwww wwwwwwwwwww wwwwwww wwwwwww qwwwwww DDDDDDDDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDDDDDDDDDD$ @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDDDDDDD$D @DDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD !@DDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDD qw'rw '""""r q23333# qwwwwww '""""r +""""""""" '$$BBr """""""""" qwwwww +""""""""" +""""""""" '$$BBr """""""""" wwwww +r"''rww"w +""""""""" '$$BBr r'rww"''r" +""""""""" +w"ww'rr"' '$$BBr """""""""" +"rrww'rwr +""""""""" '$$BBr rrw"wwwr"" +""""""""" +'w'rww''" '$$BBr """""""""" +rw'rw"'rw +""""""""" '$$BBr rw"'rw"ww" +""""""""" +w'r"w'rw' '$$BBr """""""""" +""""""""" +""""""""" '$$BBr """""""""" +""""""""" '$$BBr '""""r A$!!! qwwwwwwwwwww wwwwww qwwwwwwwwwww DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD$ @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD awww%'r twG!Wwg!' a%WwW 1ww7Aww !'ruw !'raw&sw' s$'!G f3S717 !'r'1G a63#!'r"F!G A&'1& vww7! awwG!'r BUg!7 1''Qwww 6Qwww 1g3d!7 1''Q633 r3g3dQ 6Q633 A&2w"w#t#' s%!v"w#t a&b7d"s'a f3CgA&2w"g"Vr a%r'r7BG vws&6vG!& 1vw&!GvG !vW6aw7gAg !61wG&rdw DDDDDDDDDT DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD BUUUUUUUUUU @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD qwwwwwwwwwwwwwwwwwwwwww qwwwwwwwwwwwwwwwwwwwwww wwwwwwwwwwwwwwwwwwwwww !qwwwwwwwwwwwwwwwwwwwwww p"""""""""""""""""""""r q"""""""""""""""""""""r '""""""""""""""""""""" q"""""""""""""""""""""r q"""""""""""""""""""""r q"""""""""""""""""""""r '"""""""""""""""""""""' q"""""""""""""""""""""r q"""""""""""""""""""""r q"""""""""""""""""""""r! '""""""""""""""""""""" !q"""""""""""""""""""""r q"""""""""""""""""""""r q"""""""""""""""""""""r '""""""""""""""""""""" q"""""""""""""""""""""r q"""""""""""""""""""""r q"""""""""""""""""""""r '""""""""""""""""""""" q"""""""""""""""""""""r q"""""""""""""""""""""r q"""""""""""""""""""""r '"""""""""""""""""""""' q"""""""""""""""""""""r rVueu q"""""""""""""""""""""r q"""""""""""""""""""""r '"""""""""""""""""""""' q"""""""""""""""""""""r Q7!'s q"""""""""""""""""""""r q"""""""""""""""""""""r '"""""""""""""""""""""' !q"""""""""""""""""""""r! rc'1& q"""""""""""""""""""""r q"""""""""""""""""""""r '""""""""""""""""""""" q"""""""""""""""""""""r !'!Tu q"""""""""""""""""""""r q"""""""""""""""""""""r '"""""""""""""""""""""' r"""""""""""""""""""""r !'s6C q"""""""""""""""""""""r q"""""""""""""""""""""r '""""""""""""""""""""" q"""""""""""""""""""""r q"""""""""""""""""""""r!!q"""""""""""""""""""""r '""""""""""""""""""""" r"""""""""""""""""""""r !'d"s'!' Q67!7 q"""""""""""""""""""""r q"""""""""""""""""""""r '""""""""""""""""""""" q"""""""""""""""""""""r !&aw7g!& q"""""""""""""""""""""r q"""""""""""""""""""""r '""""""""""""""""""""" q"""""""""""""""""""""r q"""""""""""""""""""""r q"""""""""""""""""""""r '"""""""""""""""""""""' q"""""""""""""""""""""r !q"""""""""""""""""""""r q"""""""""""""""""""""r '""""""""""""""""""""" q"""""""""""""""""""""r q"""""""""""""""""""""r q"""""""""""""""""""""r !'""""""""""""""""""""" r"""""""""""""""""""""r qwwwwwwwwwwwwwwwwwwwwww !qwwwwwwwwwwwwwwwwwwwwww wwwwwwwwwwwwwwwwwwwwww qwwwwwwwwwwwwwwwwwwwwww @DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww